Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to convert rows into columns

Author  Topic 

sveesam
Starting Member

2 Posts

Posted - 2014-08-21 : 09:36:07
I've a table like below

ColA ColB
A 12
B 22
C 45
A 56
A 34
B 23

and I need to convert the rows into column names like below

A B C A1 A2 B1
12 22 45 56 34 23


could anyone help me with the query?

Thanks in advance
Suresh

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-21 : 09:56:02
Here's something to get you started:


declare @t table (ColA char(1), ColB int)
insert into @t (ColA, ColB) values
('A', 12),
('B', 22),
('C', 45),
('A', 56),
('A', 34),
('B', 23)

select A0, B0, C0, A1, B1, C1, A2, B2, C2
from (
select colA + cast(-1+ROW_NUMBER() over(partition by colA order by colA) as char(1)) as ColA
, t.ColB
from @t t
) src

pivot (max(colB) for colA in (A0, B0, C0, A1, B1, C1, A2, B2, C2))pvt


Note that your input table does not have any sequence information. You cannot assume that SQL will return rows from your table in insertion order, since a table is a set and therefore unordered by definition. I assigned row numbers to get around this problem, but that doesn't mean that the row numbers will be assigned in insertion order either.

BTW this solution is not very scalable. What if you had 1000 rows for 'A'? 10000 rows? a million rows?

SQL has a limit of 4096 columns per query: http://msdn.microsoft.com/en-us/library/ms143432.aspx

Also, if you want to eliminate the Null columns in the result of the query above, you can do it with dynamic SQL.
Go to Top of Page
   

- Advertisement -