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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Help Merging Rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

qman
Constraint Violating Yak Guru

USA
440 Posts

Posted - 04/08/2013 :  10:32:44  Show Profile  Reply with Quote
I am trying to merge rows into one row for each "Sym", and create aliased colums with the merged data. I am hoping my sample table will make more sense than my cryptic babbling.

TABLE A

Sym	Name	      Title
8	Abe Lincoln   President
33	Bill Gates    CEO
33	George Wash   President


I am trying to accomplish this:

Sym	Name1	      Title1     Name2        Title2
8       Abe Lincoln   President  NULL         NULL
33	Bill Gates    CEO        George Wash  President

Thanks!

James K
Flowing Fount of Yak Knowledge

3719 Posts

Posted - 04/08/2013 :  11:15:50  Show Profile  Reply with Quote
;with cte as
(
	select *, ROW_NUMBER() over (partition by Sym order by Title) as RN
	from TableA
)
select
	Sym,
	MAX(CASE WHEN RN = 1 THEN Name END) as Name1,
	MAX(CASE WHEN RN = 1 THEN Title END) as Title1,
	MAX(CASE WHEN RN = 2 THEN Name END) as Name2,
	MAX(CASE WHEN RN = 2 THEN Title END) as Title2
from
	cte;
Go to Top of Page

qman
Constraint Violating Yak Guru

USA
440 Posts

Posted - 04/08/2013 :  13:07:51  Show Profile  Reply with Quote
Good stuff James, thanks!
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3719 Posts

Posted - 04/08/2013 :  15:01:11  Show Profile  Reply with Quote
You are very welcome - glad to help.
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.11 seconds. Powered By: Snitz Forums 2000