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!!
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
