Get rows of table as columns in second table

Question

I apologize in advance if this question has been asked before.

I have two tables. Table one has three columns CustomerID, SequenceNum, Value, table two has a large amount of columns. I would like to fill in the columns of table two with the values of table one by column, not by row.

An 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 essence. Each unique CustomerID in table one will have a single row in table two. Each SequenceNum of a particular CustomerID will fill in a column in table two under PrimaryVal, OtherVal1, OtherVal2, etc.. A row which has a SequenceNum equal to 1 will fill the PrimaryVal field, and 2-18 (the maximum sequence length is 18) will fill OtherVal#.

The main problem I see is the variable amount of values in a sequence. Some sequences may only contain 1 row, some will fill up all 18 spots, and anything in between.

Any advice on how to solve this problem would be greatly appreciated. Thank you.

@MikeMiller check my recent edit. I don't see how pivot can be used if the sequences may be of variable sizes — Adam, Jul 09 '15 at 14:52
have you tried something like select customerID, Pivoted.* from Customer pivot( Value for sequencenum in (1,2,3,4,5,6, upto 18)) as Pivoted — Mike Miller, Jul 09 '15 at 14:58
@MikeMiller If SequenceNum only goes to 5 on a certain CustomerID, wouldn't that result in an error since 6-18 does not exist? — Adam, Jul 09 '15 at 15:00
I think you'll just get nulls, I'm not in front of SQL box at the moment, try it with a SQLFiddle — Mike Miller, Jul 09 '15 at 15:01
@MikeMiller that worked. If you want to put it as an answer, I'll accept it for you — Adam, Jul 09 '15 at 15:14

score 2 · Accepted Answer · answered Jul 09 '15 at 15:48

2

Given you know that it is 18 columns max, I would take the normal pivot route.

select customerID, Pivoted.*
 from Customer
 pivot( Value for sequencenum in (1,2,3,4,5,6, upto 18)) as Pivoted

I've been lazy here and not aliased the columns but you can if you need to.

answered Jul 09 '15 at 15:48

Mike Miller

16,195
1
20
27

Agree to use fixed if the columns aren't dynamic. You'll just need to add an aggregate and also escape out the column names, i.e. `pivot( Max(Value) for sequencenum in ([1],[2],[3],[4],[5],[6] ... ))` – StuartLC Jul 09 '15 at 16:34

score 1 · Answer 2 · edited May 23 '17 at 12:14

This can be done with a Dynamic Pivot. The first STUFF Select (or any other GROUP_CONCAT hack) is used to determine the columns needed (based on the values of SequenceNum) before applying this into a dynamic pivot which then assigns the values to these columns.

You'll need to take an opinion on an aggregate during the pivot (I've used Min), although if there aren't duplicate CustomerId, SequenceNum tuples, this is a fairly 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

Get rows of table as columns in second table

2 Answers2