1

Have tried numerous SQL fiddles but got nowhere fast. I have the following in a table

Col1 | Col2 | Col3
-------------------
 A   | B    | C
 D   | E    | F
 G   | H    | I

I need the output to be

 A   | D    | G
 B   | E    | H
 C   | F    | I

Please can someone help. I understand a PIVOT might be the best bet?

Thanks

pee2pee
  • 3,619
  • 7
  • 52
  • 133
  • Did you try to google for "SQL Server Rows to columns"? There are numerous solutions already available. And yes, PIVOT is one solution. – Lmu92 Aug 13 '14 at 12:25
  • Yes and they gave results such as http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server where the data within the table is known. For me, it's not known – pee2pee Aug 13 '14 at 12:26

1 Answers1

3

Start by unpivotting it, then push it back the other way.

with --Build up as we go
--Start with original data
t (Col1, Col2, Col3)
as 
(          select 'A','B','C'
 union all select 'D','E','F'
 union all select 'G','H','I')
,
--Put rownums in there - they'll help
numbered as (
select row_number() over (order by Col1) as rownum, *
from t)
,
--Now unpivot the data, using cross apply.
unpivotted as (
select r.rownum, c.colnum, c.val
from numbered r
cross apply (values (1, Col1), (2, Col2), (3, Col3)) as c (colnum, val)
)
--Now pivot it back again, grouping by colnum
select max(case when rownum = 1 then val end), 
       max(case when rownum = 2 then val end), 
       max(case when rownum = 3 then val end)
from unpivotted
group by colnum;
Rob Farley
  • 15,625
  • 5
  • 44
  • 58