SQL Query - pivot without aggregate function

Question

I have a table which looks like the table given below. This table holds the information about students and their answers to some multiple choice questions. Consider that number of questions might be different in each exam.

+--------+---------------+-------------+
| Person | QuestionIndex | ChoiceIndex |
+--------+---------------+-------------+
| John   |             1 |          01 |
| John   |             2 |          02 |
| John   |             3 |          04 |
| Peter  |             1 |          01 |
| Peter  |             2 |          03 |
| Peter  |             3 |          04 |
| Jack   |             1 |          01 |
| Jack   |             2 |          02 |
| Jack   |             3 |          03 |
+--------+---------------+-------------+

Now, I would like to change rows to columns using pivot to a layout such as this:

+--------+----+----+----+
| Person | Q1 | Q2 | Q3 |
+--------+----+----+----+
| John   | 01 | 02 | 04 |
| Peter  | 01 | 03 | 04 |
| Jack   | 01 | 02 | 03 |
+--------+----+----+----+

Which columns are question indexes. and each field hold the choice of the user for that question in a multiple choice exam.

I would like to do this using pivot. Can anybody help doing this?

https://stackoverflow.com/questions/1343145/tsql-pivot-without-aggregate-function?noredirect=1&lq=1 — David Baker, May 07 '18 at 17:07

Daniel Marcus · Answer 1 · 2018-05-07T19:26:57.820

1

Assuming your original table is #temp:

     declare @iterator int =1
     declare @iterator2 varchar(max) =  cast(@iterator as varchar(max)) 
     declare @maxid int =(select max(questionindex) from #temp)
     declare @exec varchar(max) =  ''
     declare @holding table (rowid int identity, string varchar(max))
     while @iterator<=@maxid
     begin 
     insert @holding 
     select 'max(case when questionindex='+@iterator2+' then choiceindex else null end)Q'+@iterator2+','
     set @iterator=@iterator+1 
     end
     select @maxid=max(rowid) from @holding
     set @iterator =1
     while @iterator<=@maxid
     begin 
     select @exec= @exec+string from @holding where rowid=@iterator
     set @iterator=@iterator+1
     end 

    select @exec= 'select person, '+left(@exec, len(@exec)-1)+' from #temp group by person'

    exec(''+@exec+'')

edited May 07 '18 at 19:26

answered May 07 '18 at 16:15

Daniel Marcus

2,686
1
7
13

The provided code works; however, It's not dynamic. I need a query which works for different numbers of questions. And if I want to generate this SQL statement manually, It would be a long text which doesn't fit in a nvarchar(max) variable to be executed. – Shahram Akbarinasaji May 07 '18 at 18:53
Please have a look at this blog post and let me know if it is helpful: https://thesqlserverdeveloper.blogspot.com/2018/02/performing-dynamic-pivot.html?view=magazine – Daniel Marcus May 07 '18 at 18:59
I adjusted the code above to be dynamic. It should work regardless of how many questionindex values you have. Please have a look. – Daniel Marcus May 07 '18 at 19:26

score 1 · Accepted Answer · answered May 07 '18 at 16:20

You can use PIVOT like the following code, I have created a common table expression named 'cte_base' that separates grouping column, spreading column and aggregation column. Then I have applied PIVOT to data from CTE.

WITH cte_base AS(
SELECT Person,      --Grouping column
QuestionIndex,      --Spreading column
ChoiceIndex         --Aggregation column
FROM tablename)
SELECT person, 
[01] AS 'Q1',
[02] AS 'Q2',
[03] AS 'Q3'
FROM cte_base
PIVOT (MAX(ChoiceIndex) 
FOR QuestionIndex IN ([01],[02],[03]));

SQL Query - pivot without aggregate function

2 Answers2