Crosstab Query with Dynamic Columns in SQL Server

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.

Answer 1

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

Answer 2

This requires building a SQL query string at runtime. Column names, counts and data-types in SQL Server are always static (the most important reason for that is that the optimizer must know the query data flow at optimization time).

So I recommend that you build a PIVOT-query at runtime and run it through sp_executesql. Note that you have to hardcode the pivot-column values. Be careful to escape them properly. You cannot use parameters for them.

Alternatively you can build one such query per column-count and use parameters just for the pivot values. You would have to assign some dummy column names like Pivot0, Pivot1, .... Still you need one query template per count of columns. Except if you are willing to hard-code the maximum number of pivot-columns into the query (say 20). In this case you actually could use static SQL.

Answer 3

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