Home > OS >  Converting rows to columns without any calculation
Converting rows to columns without any calculation

Time:01-09

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;  


  • Related