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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to convert rows into columns
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 08/21/2014 :  09:36:07  Show Profile  Reply with Quote
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

Flowing Fount of Yak Knowledge

2780 Posts

Posted - 08/21/2014 :  09:56:02  Show Profile  Reply with Quote
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:

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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000