SQL group data based on a column and then transpose rows to columns

Question

I'm trying to transpose my SQL table rows to columns but I need the resulting rows to be created based on the groups in the 'Quiz' column.

I have read threads about transposing rows to columns but since what I'm trying to achieve involves grouping a column and I need to use all values in the 'Score' column, I can't figure out how to achieve my desired outcome.

Original table:

 Quiz |  Name  |  Score
---------------------------
  A   |  Andy  |  10
  A   |  Drew  |  8
  A   |  Mark  |  7
  A   |  Samm  |  6
  B   |  Andy  |  9
  B   |  Drew  |  7
  B   |  Mark  |  7
  B   |  Samm  |  9

Query result:

Quiz  |  Andy |  Drew  |  Mark  |  Samm
-----------------------------------------
  A   |   10  |    8   |   7    |  6
  B   |    9  |    7   |   7    |  9

Any help is highly appreciated.

Does this answer your question? [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Isaac, Mar 28 '20 at 20:17

Serkan Arslan · Answer 1 · 2020-03-28T20:27:51.217

You can use PIVOT

DECLARE @SampleData TABLE (Quiz VARCHAR(5),   Name  VARCHAR(10),   Score INT)
INSERT INTO @SampleData VALUES
('A', 'Andy', 10),
('A', 'Drew', 8 ),
('A', 'Mark', 7 ),
('A', 'Samm', 6 ),
('B', 'Andy', 9 ),
('B', 'Drew', 7 ),
('B', 'Mark', 7 ),
('B', 'Samm', 9 )


SELECT * FROM @SampleData 
PIVOT ( MAX(Score) FOR Name IN ([Andy], [Drew], [Mark], [Samm])) AS PVT

If you want to make it dynamic you can use dynamic pivot

DECLARE @Columns VARCHAR(MAX) = ''
SELECT @Columns = @Columns + ',' + QUOTENAME( Name ) FROM (SELECT DISTINCT Name FROM SampleData) T
SELECT @Columns = STUFF(@Columns, 1,1,'')

DECLARE @SqlText VARCHAR(MAX) = 'SELECT * FROM SampleData 
PIVOT ( MAX(Score) FOR Name IN ('+ @Columns +')) AS PVT'

EXEC (@SqlText)

Result:

Quiz  Andy        Drew        Mark        Samm
----- ----------- ----------- ----------- -----------
A     10          8           7           6
B     9           7           7           9

score 1 · Answer 2 · answered Mar 28 '20 at 20:15

For a fixed list of names, you can do conditional aggregation:

select
    quiz,
    max(case wen name = 'Andy' then score end) Andy,
    max(case wen name = 'Drew' then score end) Drew,
    max(case wen name = 'Mark' then score end) Mark,
    max(case wen name = 'Sam'  then score end) Sam
from mytable
group by quiz

If you want something that generates columns on the fly depending on the names found in the table, then this cannot be done with pure SQL. You need dynamic SQL (ie build a query string from the values found in the table, then executing it) - this another beast.

Thanks for the answer. Yes I need dynamic SQL but I can work with that. I can build the query string on the fly. — Azizul H, Mar 30 '20 at 07:24

SQL group data based on a column and then transpose rows to columns

2 Answers2