SQL 报表查询语句，【极富挑战性】，三天了，要逼疯了，不会写，求大虾帮忙

项目中要做问卷调查的统计报表，建表语句类似下面：

CREATE TABLE [dbo].[QuestionTable](
	[id] [nchar](10) NULL,--id
	[R1] [nchar](10) NOT NULL,--问题1答案
	[R2] [nchar](10) NOT NULL,--问题2答案
	[R3] [nchar](10) NOT NULL,--问题3答案
	[R4] [nchar](10) NOT NULL,--问题4答案
	[R5] [nchar](10) NOT NULL,--问题5答案
	[R6] [nchar](10) NOT NULL,--问题6答案
	[t_user] [nchar](10) NULL --被调查者
) ON [PRIMARY]

说明：问题1~4为评分题目，分值为1到5分；问题5~6是二选一题目，是就写Y，否就写N；如果答案用户乱写（答案无效），则置为NA；

报表的样式如下：



数据为下图：


统计R1~R4的算法是 求每个user的平均分
统计R5和R6的算法是 每个user的 Y 个数/(Y个数+N个数)，以百分比呈現

注意：user是不固定的，也就是报表的列是不固定的~

请问这样的SQL语句增么写呢？望大虾们指教~

29 个解决方案

#1

我勒个去，先分组求平均和百分比
再动态行转列，实在不行数据丢出来，整个图不给力啊

#2

想不出来理由要行转列.

如果有100个user,是不是也得转为100列?

#3

*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。

问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)： 
姓名 语文 数学 物理 
---- ---- ---- ----
李四 74   84   94
张三 74   83   93
-------------------
*/

create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go

--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名

--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) 

--SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b

--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')

---------------------------------

/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分 
---- ---- ---- ---- ------ ----
李四 74   84   94   84.00  252
张三 74   83   93   83.33  250
*/

--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理,
  cast(avg(分数*1.0) as decimal(18,2)) 平均分,
  sum(分数) 总分
from tb
group by 姓名

--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql) 

--SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名

--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m , 
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')

drop table tb    

------------------
------------------

#4

动态行转列

百分比一样的求

#5

为什么要把报表格式设计成那样子，
数据库的样子不是很好吗？甚至都不需要你行列转换，直接安User分组就行，
         R1  R2  R3  R4  R5   R6
User1   4   3   2   2   80%  90%
User2   4   3   2   2   80%  90%
User3   4   3   2   2   80%  90%

#6

引用 5 楼 ranen2010 的回复:

为什么要把报表格式设计成那样子，
数据库的样子不是很好吗？甚至都不需要你行列转换，直接安User分组就行，
  R1 R2 R3 R4 R5 R6
User1 4 3 2 2 80% 90%
User2 4 3 2 2 80% 90%
User3 4 3 2 2 80% 90%

5555555555555555客戶需求就是那樣子的吖~主要是中間穿插著 ‘NA’，統計就非常麻煩了，不會做吖~

#7

引用 3 楼 fredrickhu 的回复:

SQL code

*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。

问题：假设有张学生成绩表(tb)如下:
……

關鍵是數據中穿插著 ‘NA’，動態行轉列就不好查平均值了

#8

引用 1 楼 geniuswjt 的回复:

我勒个去，先分组求平均和百分比
再动态行转列，实在不行数据丢出来，整个图不给力啊

user不是很多 1~20個，但是個數不固定

#9

'NA’怎么计算你不说清楚？

#10

引用 9 楼 nbdba 的回复:

'NA’怎么计算你不说清楚？

‘NA’ 是數據無效的意思，‘NA’不參與計算

#11

引用 9 楼 nbdba 的回复:

'NA’怎么计算你不说清楚？

例如：計算user1 的r1 平均值，就把所有user1所在行的 (r1分數總和)/(r1有效分數的個數)；
計算user1的r5的百分比，就把所有user1所在行的 （Y個數和）/(Y個數+N個數)*100   %；

#12

引用 10 楼 w22124 的回复:

引用 9 楼 nbdba 的回复:
'NA’怎么计算你不说清楚？


‘NA’ 是數據無效的意思，‘NA’不參與計算

那总数怎么算？一个用户三条记录，其中R1一个NA，r5一个na
那R1计算是另两个和/2还是/3，r5计算一样是/2还是/3

#13

不行转列写法很简单

select t_user
  ,avg(cast(nullif(R1,'NA') as numeric(10,2)) as R1
  ,avg(cast(nullif(R2,'NA') as numeric(10,2)) as R2
  ,avg(cast(nullif(R3,'NA') as numeric(10,2)) as R3
  ,avg(cast(nullif(R4,'NA') as numeric(10,2)) as R4
  ,100.0*sum(case when nullif(R5,'NA')='Y' then 1 else 0 end)/count(nullif(R5,'NA')) as R5
  ,100.0*sum(case when nullif(R6,'NA')='Y' then 1 else 0 end)/count(nullif(R6,'NA')) as R6
from QuestionTable
group by t_user

#14

引用 13 楼 nbdba 的回复:

不行转列写法很简单


SQL code

select t_user
  ,avg(cast(nullif(R1,'NA') as numeric(10,2)) as R1
  ,avg(cast(nullif(R2,'NA') as numeric(10,2)) as R2
  ,avg(cast(nullif(R3,'NA') as numeric(10,2)) as R3
……

這是SQL Server 中的語句嗎？我放在SQL2008中執行出錯了，---消息 102，级别 15，状态 1，第 3 行
Incorrect syntax near ','.-----

#15

引用 13 楼 nbdba 的回复:

不行转列写法很简单


SQL code

select t_user
  ,avg(cast(nullif(R1,'NA') as numeric(10,2)) as R1
  ,avg(cast(nullif(R2,'NA') as numeric(10,2)) as R2
  ,avg(cast(nullif(R3,'NA') as numeric(10,2)) as R3
……

已經有思路了，非常好，謝謝！
結合#3楼的動態行轉列，應該就可以搞定了~太感謝了

#16

引用 2 楼 qianjin036a 的回复:

想不出来理由要行转列.

如果有100个user,是不是也得转为100列?

很好的動態行轉列的例子~！謝謝

#17

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
	DROP TABLE #temp
GO
CREATE TABLE #temp(
    [id] [nchar](10) NULL,--id
    [R1] [nchar](10) NOT NULL,--问题1答案
    [R2] [nchar](10) NOT NULL,--问题2答案
    [R3] [nchar](10) NOT NULL,--问题3答案
    [R4] [nchar](10) NOT NULL,--问题4答案
    [R5] [nchar](10) NOT NULL,--问题5答案
    [R6] [nchar](10) NOT NULL,--问题6答案
    [t_user] [nchar](10) NULL --被调查者
)
INSERT #temp
SELECT 1,'3','3','4','5','Y','N', 'user1' UNION ALL
SELECT 2,'3','2','3','4','Y','N', 'user2' UNION ALL
SELECT 3,'2','NA','4','2','NA','Y', 'user1'
GO
--SQL:
SELECT * FROM #temp
DECLARE @sql VARCHAR(MAX)
SET @sql = '
SELECT * FROM
(
	SELECT * FROM
	(
		SELECT
			t_user,
			R1 = CAST(1.0*SUM(CASE R1 WHEN ''NA'' THEN 0 ELSE CAST(R1 AS INT) END) / COUNT(1) AS FLOAT),
			R2 = CAST(1.0*SUM(CASE R2 WHEN ''NA'' THEN 0 ELSE CAST(R2 AS INT) END) / COUNT(1) AS FLOAT),
			R3 = CAST(1.0*SUM(CASE R3 WHEN ''NA'' THEN 0 ELSE CAST(R3 AS INT) END) / COUNT(1) AS FLOAT),
			R4 = CAST(1.0*SUM(CASE R4 WHEN ''NA'' THEN 0 ELSE CAST(R4 AS INT) END) / COUNT(1) AS FLOAT),
			R5 = CAST(1.0*COUNT(CASE R5 WHEN ''NA'' THEN NULL WHEN ''N'' THEN NULL ELSE 1 END) / COUNT(CASE R5 WHEN ''NA'' THEN NULL ELSE 1 END)*100 AS FLOAT),
			R6 = CAST(1.0*COUNT(CASE R6 WHEN ''NA'' THEN NULL WHEN ''N'' THEN NULL ELSE 1 END) / COUNT(CASE R6 WHEN ''NA'' THEN NULL ELSE 1 END)*100 AS FLOAT)
		from #temp
		GROUP BY t_user
	) A
	UNPIVOT
	([R_value] FOR [R_type] IN([R1], [R2], [R3], [R4], [R5], [R6])) B
) C
PIVOT
(
	SUM([R_value]) FOR t_user IN('+ STUFF((SELECT DISTINCT ','+QUOTENAME(RTRIM([t_user])) FROM #temp FOR XML PATH('')),1,1,'') +')
) D
'

EXEC(@sql)
--RESULT:
/*
R_type	user1	user2
R1	2.5	3
R2	1.5	2
R3	4	3
R4	3.5	4
R5	100	100
R6	50	0
*/

#18

引用 16 楼 w22124 的回复:

引用 2 楼 qianjin036a 的回复:
想不出来理由要行转列.

如果有100个user,是不是也得转为100列?


很好的動態行轉列的例子~！謝謝

( ⊙o⊙ )哇！！太專業了，一步到位！！謝謝！！

#19

引用 17 楼 wwwwgou 的回复:

SQL code

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    DROP TABLE #temp
GO
CREATE TABLE #temp(
    [id] [nchar](10) NULL,--id
    [R1] [nchar](10) NOT NULL,--问题1答案
    [R2] [nchar](10) NOT NU……

( ⊙o⊙ )哇！！太專業了，一步到位！！謝謝！！

#20

引用 16 楼 w22124 的回复:

引用 2 楼 qianjin036a 的回复:
想不出来理由要行转列.

如果有100个user,是不是也得转为100列?


很好的動態行轉列的例子~！謝謝

追问一下：
假如我要把响应的 问题 列在左侧呢？类似下图中的样子:


問題表如下：

CREATE TABLE [dbo].[Qdesc](
	[id] [nchar](10) NULL,
	[Q1] [nvarchar](50) NULL,
	[Q2] [nvarchar](50) NULL,
	[Q3] [nvarchar](50) NULL,
	[Q4] [nvarchar](50) NULL,
	[Q5] [nvarchar](50) NULL,
	[Q6] [nvarchar](50) NULL
) ON [PRIMARY]

數據如下：


該怎麼一步到位做出那樣的效果呢？

#21

引用 3 楼 fredrickhu 的回复:

SQL code

*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。

问题：假设有张学生成绩表(tb)如下:
……

这位大虾好给力。。。

#22

该回复于2011-09-13 10:19:41被版主删除

#23

学习了，顶一个。

#24

复杂的sql语句呀，慢慢学习

#25

学习一下啊

#26

学习一下啊 

#27

向各位大侠学习了，真强悍啊

#28

这样的报表真奇怪，为什么不把行和列调换过来呢。
行列调换后用透视表很容易做出来。

#29

我以为来做下着个项目，具体什么情况做了才知道。