Crosstab Query with Dynamic Columns in SQL Server 2005 up

Question

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

Suppose that I have data as below:

| 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:

Subject Name:

• C
• C++
• English
• Database

• Math

  will be changed depend on which subject student learn.

Please go to http://sqlfiddle.com/#!6/2ba07/1 to test this query.

Solution 1

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.

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.

Static Version:

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

See SQL Fiddle with demo

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)

See SQL Fiddle with Demo

Both versions will yield the same results.

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

See SQL Fiddle with Demo

Solution 2

You need to use SQL PIVOT in this case. Plese refer the following link:

Pivot on Unknown Number of Columns

Pivot two or more columns in SQL Server

Pivots with Dynamic Columns in SQL Server