我不知道如何动态生成列名，如果你可以组合sql脚本，你可以使用组合字符串脚本来执行。

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;