SOLVED

Transpose rows to columns

Copper Contributor

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

best response confirmed by RBiems (Copper Contributor)
Solution

Re: Transpose rows to columns

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 pivot

DECLARE @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 #TMP2

SELECT @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 best response

Accepted Solutions
best response confirmed by RBiems (Copper Contributor)
Solution

Re: Transpose rows to columns

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 pivot

DECLARE @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 #TMP2

SELECT @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)