Query a table with dynamic columns in SQL Server 2005 up

Question

I have a problem with a Crosstab query in SQL Server.

Suppose I have data as shown 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 a query that shows the result as shown 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 can I request output output as follows?

Note:

Topic Title:

• WITH
• C ++
• English
• Database

• Math

  will vary depending on what subject the student will study.

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

Answer 1

There are two ways to execute PIVOT statics, where you hard-code values ​​and dynamic parameters when columns are defined at runtime.

Although you need a dynamic version, it is sometimes easier to start with a static PIVOT and then work in dynamic mode.

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 for a demo

Now, if you do not know the values ​​to be converted, 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 give the same results.

To complete the answer, if you do not have a PIVOT function, you can get this result using CASE and the 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

Answer 2

This requires the creation of an SQL query string at run time. Column names, counts, and data types in SQL Server are always static (the most important reason is that the optimizer needs to know the query data stream during optimization).

Therefore, I recommend that you create a PIVOT -query at run time and run it through sp_executesql . Please note that you need to hard-code column values. Be careful to avoid them properly. You cannot use parameters for them.

Alternatively, you can build one such query on the number of columns and use parameters only for values ​​of summary values. You would need to assign dummy column names, for example Pivot0, Pivot1, ... However, you need one query template for each column. Unless you are ready to hard code the maximum number of columns in a query (say, 20). In this case, you really can use static SQL.

Answer 3

In this case, you need to use SQL PIVOT. Plese refers to the following link:

Rotate on an unknown number of columns

Expand two or more columns in SQL Server

Dynamic Column Turns in SQL Server