Help Merging Rows - SQL Server Forums

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]

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2013-04-08 : 13:07:51

Good stuff James, thanks!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-08 : 15:01:11

You are very welcome - glad to help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources