Forum Discussion
Solved
Transpose rows to columns
I have the table to the left, I would like to get the right table as a result, I don't know how many columns there will be, is this possible in SQL ?
| ID | ep | ID | EP1 | EP2 | EP3 | EP4 | etc... | |||
| 1 | 67 | 1 | 67 | 118 | 132 | 234 | ||||
| 1 | 118 | 2 | 1234 | 49 | 50 | 100 | ||||
| 1 | 132 | 3 | 123 | 321 | 456 | |||||
| 1 | 234 | |||||||||
| 2 | 1234 | |||||||||
| 2 | 49 | |||||||||
| 2 | 50 | |||||||||
| 2 | 100 | |||||||||
| 3 | 123 | |||||||||
| 3 | 321 | |||||||||
| 3 | 456 |
Solution was actually easier then I though.
First I added an extra column with
'EP' + CAST(ROW_NUMBER() OVER(PARTITION by ID ORDER BY ID) as nvarchar(10)) as RNK
and then a simple dynamic pivotDECLARE @query AS NVARCHAR(MAX)='',
@colsPivot as NVARCHAR(MAX)='';CREATE TABLE #TMP3 (ID nvarchar(10), subject bigint, RNK Nvarchar(10));
insert into #TMP3 select ID, subject, 'EP' + CAST(ROW_NUMBER() OVER(PARTITION by ID ORDER BY ID) as nvarchar(10)) as RNK FROM #TMP2SELECT @colspivot = @Colspivot + quotename(rnk) + ',' from (select distinct rnk from #TMP3) as tmp3
select @colsPivot = substring(@colspivot, 0, Len(@colspivot))
select @colspivot
Set @query = 'SELECT * FROM
(select id,subject,rnk from #tmp3) T1
pivot
(SUM(Subject) for rnk in (' + @colsPivot + ')
) T2'execute(@query)
1 Reply
Solution was actually easier then I though.
First I added an extra column with
'EP' + CAST(ROW_NUMBER() OVER(PARTITION by ID ORDER BY ID) as nvarchar(10)) as RNK
and then a simple dynamic pivotDECLARE @query AS NVARCHAR(MAX)='',
@colsPivot as NVARCHAR(MAX)='';CREATE TABLE #TMP3 (ID nvarchar(10), subject bigint, RNK Nvarchar(10));
insert into #TMP3 select ID, subject, 'EP' + CAST(ROW_NUMBER() OVER(PARTITION by ID ORDER BY ID) as nvarchar(10)) as RNK FROM #TMP2SELECT @colspivot = @Colspivot + quotename(rnk) + ',' from (select distinct rnk from #TMP3) as tmp3
select @colsPivot = substring(@colspivot, 0, Len(@colspivot))
select @colspivot
Set @query = 'SELECT * FROM
(select id,subject,rnk from #tmp3) T1
pivot
(SUM(Subject) for rnk in (' + @colsPivot + ')
) T2'execute(@query)