英文:
Converting rows to columns without any calculation
问题
我有一个只有一列的表格。如何将这些行转换为列？| 颜色代码 || --- || #FFCC00 || #339966 || #800080 || #FF9900 |最多可能有10行。我期待的结果是：| C1 | C2 | C3 | C4 || --- | --- | --- | --- || #FFCC00 | #339966 | #800080 | #FF9900 |
英文:
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 |
答案1
得分: 1
我不知道如何动态生成列名，如果您可以组合SQL脚本，可以使用组合的字符串来执行脚本。
with code as (select '#FFCC00' as ColourCodeunionselect '#339966' as ColourCodeunionselect '#800080' as ColourCodeunionselect '#FF9900' as ColourCode )select *from(select ColourCode ,'C' + cast( ROW_NUMBER() OVER (Order by (select 1)) as nvarchar(max)) as rn -- 生成序列号from code ) as sourcetablePIVOT(max(ColourCode)FOR rn IN ([C1],[C2],[C3],[C4]) -- 预定义列名，如果您想要动态生成，您应该使用变量) AS PivotTable;
英文:
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 ColourCodeunionselect '#339966' as ColourCodeunionselect '#800080' as ColourCodeunionselect '#FF9900' as ColourCode )select *from(select ColourCode ,'C' + cast( ROW_NUMBER() OVER (Order by (select 1)) as nvarchar(max)) as rn -- generate sequence numberfrom code ) as sourcetablePIVOT(max(ColourCode)FOR rn IN ([C1],[C2],[C3],[C4]) -- predifned column Name, if you want to dynamic generated, you should use variable) AS PivotTable;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论