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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Help Merging Rows

Author  Topic 

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2013-04-08 : 10:32:44
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-08 : 11:15:50
[code];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;[/code]
Go to Top of Page

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2013-04-08 : 13:07:51
Good stuff James, thanks!
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-08 : 15:01:11
You are very welcome - glad to help.
Go to Top of Page
   

- Advertisement -