Get table rows as columns in second table
I apologize in advance if this question has been asked before.
I have two tables. Table 1 has three columns CustomerID, SequenceNum, Value
, Table 2 has a large number of columns. I would like to fill the columns of table 2 with the table values by column, not by row.
Example:
------------------------------------
| CustomerID | SequenceNum | Value |
------------------------------------
| 1 | 1 | A |
------------------------------------
| 1 | 2 | B |
------------------------------------
| 1 | 3 | C |
------------------------------------
| 2 | 1 | Q |
------------------------------------
| 2 | 2 | R |
------------------------------------
| 3 | 1 | X |
------------------------------------
becomes
---------------------------------------------------------------------
| CustomerID | PrimaryVal | OtherVal1 | OtherVal2 | OtherVal3 | ... |
---------------------------------------------------------------------
| 1 | A | B | C | NULL | ... |
---------------------------------------------------------------------
| 2 | Q | R | NULL | NULL | ... |
----------------------------------------------------------------------
| 3 | X | NULL | NULL | NULL | ... |
---------------------------------------------------------------------
In fact. Each unique CustomerID
in table one will have one row in table 2. Each SequenceNum
specific one will CustomerID
populate a column in table 2 in the section PrimaryVal, OtherVal1, OtherVal2, etc.
. A line that has SequenceNum
1 will fill the field PrimaryVal
, and 2-18 (maximum sequence length is 18) will fill OtherVal#
.
The main problem I see is the variable magnitude of the values in the sequence. Some sequences can contain only one line, some will fill all 18 spots and something in between.
Any advice on how to fix this issue would be greatly appreciated. Thank.
Considering that you know this is 18 columns max, I would take the normal circular route.
select customerID, Pivoted.*
from Customer
pivot( Value for sequencenum in (1,2,3,4,5,6, upto 18)) as Pivoted
I was lazy here and didn't use column aliases, but you can if you need to.
This can be done using Dynamic Pivot. The first STUFF
Select (or any other GROUP_CONCAT
hack ) is used to define the required columns (based on values SequenceNum
) before applying this to the dynamic bar, which then assigns values to those columns.
You will need to give an opinion on the aggregate during the pivot (I used Min
), although if there are no duplicate tuples CustomerId, SequenceNum
this is a pretty arbitrary choice:
DECLARE
@cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(SequenceNum)
FROM Table1
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'');
set @query = N'SELECT CustomerID, ' + @cols + N' from
Table1
pivot
(
min(Value)
for SequenceNum in (' + @cols + N')
) p ';
execute(@query);
SqlFiddle here