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语句增么写呢？望大虾们指教~

解决方案 »

  1.   

    想不出来理由要行转列.如果有100个user,是不是也得转为100列?
      

  2.   

    *
    标题：普通行列转换(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    ------------------
    ------------------
      

  3.   

    为什么要把报表格式设计成那样子，
    数据库的样子不是很好吗？甚至都不需要你行列转换，直接安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%
      

  4.   


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

  5.   


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

  6.   


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

  7.   

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

  8.   

    不行转列写法很简单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
      

  9.   


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

  10.   

    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
    */
      

  11.   


    追问一下：
    假如我要把响应的 问题 列在左侧呢？类似下图中的样子:
    問題表如下：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]
    數據如下：
    該怎麼一步到位做出那樣的效果呢？