pivot - SQL server : Convert rows into columns

Question

Output from CTE:

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

I want to convert the rows into columns like:

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

Please let me know the best approach for this.

Thanks in advance!!

Answer 1

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;

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