SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

sveesam
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
Suresh

gbritton
Flowing Fount of Yak Knowledge

1448 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: 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
  Previous Topic Topic Next Topic  
 New 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.03 seconds. Powered By: Snitz Forums 2000