SQL Server:将行转换为列 [英] SQL server : Convert rows into columns

问题描述

CTE的输出:

colName
-----------------------
branch1    
branch1    
branch1    
unclassified

我想将行转换为列，例如:

I want to convert the rows into columns like:

 colName         colName    colName    colName
 ---------------------------------------------
 unclassified    branch1    branch1    branch1

请让我知道最佳方法.

提前谢谢！

推荐答案

如果没有看到完整的查询，我建议您在CTE中添加row_number()，然后

Without seeing your full query I would suggest adding a row_number() to your CTE and then pivoting the data based on the row number:

;with cte as
(
    select *,  -- replace * with your column names
        ROW_NUMBER() over(partition by colName order by colName) rn
    from yourdata
)
select [1] as colName1, 
    [2] as colName2, 
    [3] as colName3, 
    [4] as colName4
from cte
pivot
(
    max(colName)
    for rn in ([1], [2], [3], [4])
) piv;

如果您不想使用PIVOT函数，那么还可以使用带有CASE表达式的聚合函数:

If you do not want to use the PIVOT function, then you could also use an aggregate function with a CASE expression:

;with cte as
(
    select *,  -- replace * with your column names
        ROW_NUMBER() over(partition by colName order by colName) rn
    from yourdata
)
select 
    MAX(case when rn = 1 then colName end) colName1,
    MAX(case when rn = 2 then colName end) colName2,
    MAX(case when rn = 3 then colName end) colName3,
    MAX(case when rn = 4 then colName end) colName4
from cte
-- group by other columns in select if needed

这篇关于SQL Server:将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！