SQL Summarise Multiple Rows in a Single Row

Question

I've been through and seen this question asked in a million different ways, but none of the answers seem to fit my particular needs, so I'm hoping someone can help.

I have a table like below:

URN | CustomerID | Selection
----------
1 | 1 | 16A
----------
2 | 1 | 16B
----------
3 | 1 | 16C
----------
4 | 2 | 16A
----------
5 | 2 | 16C
----------
6 | 1 | 16D
----------
6 | 1 | 16E
----------

What I'd like is an export table or query that looks like the below (limited to 5 columns for selection):

CustomerID | Selection 1 | Selection 2 | Selection 3 | Selection 4 | Selection 5
----------
1 | 16A | 16B | 16C | 16D | 16E
----------
2 | 16A | 16C 
----------

Possible duplicate of http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — Anand, Dec 16 '16 at 17:15

score 0 · Answer 1 · answered Dec 16 '16 at 17:13

You can do this by using the ANSI-standard row_number() with a pivoting method. I prefer conditional aggregation:

select CustomerID,
       max(case when seqnum = 1 then selection end) as selection_1,
       max(case when seqnum = 2 then selection end) as selection_2,
       max(case when seqnum = 3 then selection end) as selection_3,
       max(case when seqnum = 4 then selection end) as selection_4,
       max(case when seqnum = 5 then selection end) as selection_5
from (select t.*,
             row_number() over (partition by CustomerID order by urn) as seqnum
      from t
     ) t
group by CustomerID;

John Cappelletti · Answer 2 · 2016-12-16T17:45:11.027

In case you need to go dynamic and assuming SQL Server

Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName(concat('Selection ',Row_Number() over (Partition By CustomerID Order By URN) )) From YourTable For XML Path('')),1,1,'') 
Select  @SQL = '
 Select [CustomerID],' + @SQL + '
  From  (Select CustomerID,Selection,Col=concat(''Selection '',Row_Number() over (Partition By CustomerID Order By URN) ) From YourTable) A
 Pivot (Max([Selection]) For [Col] in (' + @SQL + ') ) p'
Exec(@SQL);

Returns

CustomerID  Selection 1 Selection 2 Selection 3 Selection 4 Selection 5
1           16A         16B         16C         16D         16E
2           16A         16C         NULL        NULL        NULL

SQL Summarise Multiple Rows in a Single Row

2 Answers2