英文:
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 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 -- 生成序列号
from code ) as sourcetable
PIVOT
(
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 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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论