I have a table with only a single column. How can I convert these rows to columns?
ColourCode |
---|
#FFCC00 |
#339966 |
#800080 |
#FF9900 |
The maximum possible number of rows will be 10.
I am expecting this:-
C1 | C2 | C3 | C4 |
---|---|---|---|
#FFCC00 | #339966 | #800080 | #FF9900 |
CodePudding user response:
I don't know how to dynamic generated column name, if you can combie sql script, you can use combined string to script to execute .
with code as (
select '#FFCC00' as ColourCode
union
select '#339966' as ColourCode
union
select '#800080' as ColourCode
union
select '#FF9900' as ColourCode )
select *
from
(select ColourCode ,
'C' cast( ROW_NUMBER() OVER (Order by (select 1)) as nvarchar(max)) as rn -- generate sequence number
from code ) as sourcetable
PIVOT
(
max(ColourCode)
FOR rn IN ([C1],[C2],[C3],[C4]) -- predifned column Name, if you want to dynamic generated, you should use variable
) AS PivotTable;