SQL Server 2005中具有动态列的交叉表查询 [英] Crosstab Query with Dynamic Columns in SQL Server 2005 up

问题描述

我在SQL Server中使用交叉表查询时遇到问题.

I'm having a problem with Crosstab query in SQL Server.

假设我的数据如下:

| ScoreID | StudentID |      Name |    Sex | SubjectName | Score |
------------------------------------------------------------------
|       1 |         1 | Student A |   Male |           C |   100 |
|       2 |         1 | Student A |   Male |         C++ |    40 |
|       3 |         1 | Student A |   Male |     English |    60 |
|       4 |         1 | Student A |   Male |    Database |    15 |
|       5 |         1 | Student A |   Male |        Math |    50 |
|       6 |         2 | Student B |   Male |           C |    77 |
|       7 |         2 | Student B |   Male |         C++ |    12 |
|       8 |         2 | Student B |   Male |     English |    56 |
|       9 |         2 | Student B |   Male |    Database |    34 |
|      10 |         2 | Student B |   Male |        Math |    76 |
|      11 |         3 | Student C | Female |           C |    24 |
|      12 |         3 | Student C | Female |         C++ |    10 |
|      13 |         3 | Student C | Female |     English |    15 |
|      14 |         3 | Student C | Female |    Database |    40 |
|      15 |         3 | Student C | Female |        Math |    21 |
|      16 |         4 | Student D | Female |           C |    17 |
|      17 |         4 | Student D | Female |         C++ |    34 |
|      18 |         4 | Student D | Female |     English |    24 |
|      19 |         4 | Student D | Female |    Database |    56 |
|      20 |         4 | Student D | Female |        Math |    43 |

我要查询显示结果如下:

I want to make query which show the result as below:

| StuID| Name      | Sex    | C  | C++ | Eng | DB | Math | Total | Average |
|  1   | Student A | Male   | 100|  40 | 60  | 15 |  50  |  265  |   54    |
|  2   | Student B | Male   | 77 |  12 | 56  | 34 |  76  |  255  |   51    |
|  3   | Student C | Female | 24 |  10 | 15  | 40 |  21  |  110  |   22    |
|  4   | Student D | Female | 17 |  34 | 24  | 56 |  43  |  174  |   34.8  |

如何查询显示这样的输出?

注意:

How could I query to show output like this?

Note:

主题名称:

• C
• C ++
• 英语
• 数据库

• 数学

• C
• C++
• English
• Database

• Math

将根据学生学习的学科而改变.

will be changed depend on which subject student learn.

请转到 http://sqlfiddle.com/#!6/2ba07/1测试此查询.

推荐答案

有两种方法可以执行PIVOT静态(在其中对值进行硬编码)和动态(在其中执行时确定列).

There are two ways to perform a PIVOT static where you hard-code the values and dynamic where the columns are determined when you execute.

即使您想要一个动态版本，有时从静态PIVOT开始然后向动态版本过渡也更容易.

Even though you will want a dynamic version, sometimes it is easier to start with a static PIVOT and then work towards a dynamic one.

静态版本:

SELECT studentid, name, sex,[C], [C++], [English], [Database], [Math], total, average
from 
(
  select s1.studentid, name, sex, subjectname, score, total, average
  from Score s1
  inner join
  (
    select studentid, sum(score) total, avg(score) average
    from score
    group by studentid
  ) s2
    on s1.studentid = s2.studentid
) x
pivot 
(
   min(score)
   for subjectname in ([C], [C++], [English], [Database], [Math])
) p

请参见带有演示的SQL小提琴

现在，如果您不知道将要转换的值，则可以为此使用动态SQL:

Now, if you do not know the values that will be transformed then you can use Dynamic SQL for this:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(SubjectName) 
                    from Score
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')



set @query = 'SELECT studentid, name, sex,' + @cols + ', total, average
              from 
             (
                select s1.studentid, name, sex, subjectname, score, total, average
                from Score s1
                inner join
                (
                  select studentid, sum(score) total, avg(score) average
                  from score
                  group by studentid
                ) s2
                  on s1.studentid = s2.studentid
            ) x
            pivot 
            (
                min(score)
                for subjectname in (' + @cols + ')
            ) p '

execute(@query)

请参见带有演示的SQL提琴

两个版本都会产生相同的结果.

Both versions will yield the same results.

只需四舍五入即可得到答案，如果您没有PIVOT函数，则可以使用CASE和聚合函数来获得以下结果:

Just to round out the answer, if you do not have a PIVOT function, then you can get this result using CASE and an aggregate function:

select s1.studentid, name, sex, 
  min(case when subjectname = 'C' then score end) C,
  min(case when subjectname = 'C++' then score end) [C++],
  min(case when subjectname = 'English' then score end) English,
  min(case when subjectname = 'Database' then score end) [Database],
  min(case when subjectname = 'Math' then score end) Math,
  total, average
from Score s1
inner join
(
  select studentid, sum(score) total, avg(score) average
  from score
  group by studentid
) s2
  on s1.studentid = s2.studentid
group by s1.studentid, name, sex, total, average

请参见带有演示的SQL提琴

这篇关于SQL Server 2005中具有动态列的交叉表查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！