CODEWITHSUNDEEP
sql-server
Riverchimp
Riverchimp

Reputation: 429

SQL Server Pivot to unknown number of columns

I have data that looks like this:

   id    | serialNo
 0245DS6 | 05813542
 0245DS6 | 05813543
 0245DS6 | 05813544
 2231VC7 | 06885213
 5432PS1 | 01325131
 5432PS1 | 01325132

And I need to output it like this:

    id    | serial_1 | serial_2 | serial_3 | ...
  0245DS6 | 05813542 | 05813543 | 05813544 | ...
  2231VC7 | 06885213 |          |          | 
  5432PS1 | 01325131 | 01325132 |          |

I don't know how many serial numbers there are per id (will most likely not be greater than 10), and the number varies for each id. I think pivot is what I need to use, but I don't know enough about SQL to know what answers to other questions are useful for me, or if this is even possible.

Upvotes: 3

Views: 1575

Answers (1)

Arion
Arion

Reputation: 31239

I am just going to start with saying that this is going to be fun (and a bit evil).

Table structure:

CREATE TABLE #temp
(
    id   VARCHAR(100), 
    serialNo VARCHAR(100)
);

Test data

INSERT INTO #temp
VALUES
('0245DS6','05813542'),
('0245DS6','05813543'),
('0245DS6','05813544'),
('2231VC7','06885213'),
('5432PS1','01325131'),
('5432PS1','01325132')

Then get the unique groups:

DECLARE @columns VARCHAR(MAX)=
(
    STUFF(
    (
    Select ','+QUOTENAME(CAST(rowId AS VARCHAR(100))) AS [text()]
    FROM
    (
        SELECT DISTINCT 
            ROW_NUMBER() OVER(PARTITION BY id order by serialNo) AS rowId
        FROM #temp
    ) as tbl
    For XML PATH ('')
    )
    ,1,1,'')
)

And then execute a dynamic pivot:

DECLARE @query NVARCHAR(MAX)='SELECT
    *
FROM
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY id order by serialNo) as rowId,
        id,
        serialNo
    FROM
        #temp 
)AS sourceTable 
PIVOT
(
    MAX(serialNo)
    FOR rowId IN ('+@columns+')
) AS pvt'

EXECUTE sp_executesql @query

Result:

Id        1           2           3
-------------------------------------------
0245DS6   05813542    05813543    05813544
2231VC7   06885213    NULL        NULL
5432PS1   01325131    01325132    NULL

Upvotes: 3

Related Questions