Merge multiple rows into single row

Question

Merge multiple rows into single row

smeet.sinha

Mr or Mrs. 500

Points: 577
More actions
May 29, 2009 at 6:24 am

#210065

Hi
I want to merge multiple rows into single row with filter condition on trd_id.
Will appritiate if someone could help me out.....
Thanks
Smeet
SAMPLE DATA:
trd_id state resi1 resi2 mobile Off1 Off2
======================================================
100139NEW345345NULLNULL NULLNULL
100139OLDNULLNULL NULLNULL
100139NEWNULL34534NULL NULLNULL
100139OLDNULLNULL NULLNULL
100349NEWNULLNULL NULLNULL
100349OLD1NULLNULL NULLNULL
100349NEWNULL2NULL NULLNULL
100349OLDNULLNULL NULLNULL
100350NEWNULLNULL5 NULLNULL
100350OLDNULLNULL34534553NULLNULL
100350NEWNULL4587NULL NULLNULL
100350OLDNULL784NULL NULLNULL
Output should be like this:-
trd_id state resi1 resi2 mobile off1 off2
======================================================
100139NEW34534534534NULL NULLNULL
100139OLDNULL NULLNULL
100349NEW2NULL NULLNULL
100349OLD1NULL NULLNULL
100350NEWNULL45875 NULLNULL
100350OLDNULL78434534553NULLNULL

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

john.arnott SSChampion Points: 11882 More actions · Answer 1

This could probably be solved using aggregate functions with Group by. Your sample data is inconsistent in that not all rows specify the same number of columns. Also, even if they did, you haven't indicated what to do with the Off1 and Off2 columns -- you'll want to decide if they should be aggregated or used as grouping factors in the Group By.

Please respond as to whether this code helps, and meanwhile do also take a look at these links to help get the most out of posting a question.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxcreate table #temp (trd_id int, state char(3), resi1 int, resi2 int, mobile int, Off1 int, Off2 int)

Insert #temp

select 100139, 'NEW', 345345, NULL, NULL, NULL, NULL

union all select 100139, 'OLD', NULL, NULL, NULL, NULL, NULL

union all select 100139, 'NEW', NULL, 34534, NULL, NULL, NULL

union all select 100139, 'OLD', NULL, NULL, NULL, NULL, NULL

union all select 100149, 'NEW', NULL, NULL, NULL, NULL, NULL

union all select 100149, 'OLD', 1, NULL, NULL, NULL, NULL

union all select 100149, 'NEW', NULL, 2, NULL, NULL, NULL

union all select 100149, 'OLD', NULL, NULL, NULL, NULL, NULL

union all select 100150, 'NEW', NULL, NULL, 5, NULL, NULL

union all select 100150, 'OLD', NULL, NULL, 34534553, NULL, NULL

union all select 100150, 'NEW', NULL, 4587, NULL, NULL, NULL

union all select 100150, 'OLD', NULL, 784, NULL, NULL, NULL

SELECT Trd_ID, State, max(resi1), max(resi2), max(mobile)

from #temp

group by Trd_ID, State

order by Trd_ID, State

john.arnott SSChampion Points: 11882 More actions · Answer 2

Hello, again. I see now that you asked a similar question about a month ago.

http://www.sqlservercentral.com/Forums/Topic705818-338-1.aspx

Chris offered a possible solution that, like the one I just suggested here, uses aggregates and Group By. Did that work for you?

It's expected that people respond with whether an answer is helpful. Forum etiquette demands a give and take so that everyone can profit from the experience. You may get advice on aspects of your situation that hadn't occurred to you. People offering advice appreciate feedback on how well they've understood a situation and on the value of their suggestions. Third parties may find the thread of posts and learn something from your situation that they can apply to their own.