SQL Server Pivot for 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 identifier (most likely it won't be more than 10) and the number changes for each identifier. I think for pivot I should be using, but I don't know enough about SQL to know what answers to other questions are helpful to me, or if it's possible.
+3
source to share
1 answer
I'm just going to start off by saying that it's going to be fun (and a little spiteful).
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 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 do dynamic vault:
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
+3
source to share