Row concatenation - 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

General SQL Server Forums

New to SQL Server Programming

Row concatenation

Author

Topic

BJHennegan
Starting Member

6 Posts

Posted - 2012-04-17 : 17:00:05

I have a table that has the following configuration:


ID  Name  Guestname  GuestOn GuestlistMon  GuestlistTue
1   Bob   
1   Bob   George     Mon
1   Bob   Mary       Mon
1   Bob   Sam        Mon
2   Tom
3   Cindy 
3   Cindy Peter      Tue
3   Cindy Paul       Tue
3   Cindy Mary       Tue
4   Harry

And the final result should be:


ID  Name  Guestname  GuestOn GuestlistMon        GuestlistTue
1   Bob                      George, Mary, Sam                
1   Bob   George     Mon
1   Bob   Mary       Mon
1   Bob   Sam        Mon
2   Tom
3   Cindy                                         Peter, Paul, Mary
3   Cindy Peter      Tue
3   Cindy Paul       Tue
3   Cindy Mary       Tue
4   Harry

Essentially, I need to ignore Tom and Harry and build guestlists for Bob and Cindy by concatenating the rows and placing the result in a column on the solo (non guest) record.

I've tried to get the rows with invited guests and even throw them all into a variable in list form. I'm having trouble, however, getting the list for ONLY the invite-or (ie, I get all guests in a single list) and wrapping my head around how to update.


select ID, Name, GuestName, GuestOn
	into tmp_guest_merge
	from information
	where GuestOn is not null 


DECLARE @MonNames VARCHAR(1000)  
SELECT @MonNames = COALESCE(@MonNames + ', ', '') + GuestName 
	FROM tmp_guest_merge 
	Where GuestOn = 'Monday'

Note that I do not have the option of altering the table or varying how the end result should be presented. Another scenario that I didn't present is that there could be guests on 'Both' days, so the guestlist field would need to be filled in for both days.

Thanks for any help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-18 : 00:40:11

Please explain on what basis you exclude Tom and Harry from data modification

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BJHennegan
Starting Member

6 Posts

Posted - 2012-04-18 : 09:11:23

Since Tom and Harry do not have any invited guests.

Perhaps "ignore" was the wrong word choice. There are no rows for Tom or Harry that have invited guests, so nothing would be entered for their guestlists (or blank guestlists could be entered, if that makes it simpler).

The select as I have it written currently does not pull any solo rows. The temp table has only invited guests, which once concatenated would need to be written back to the matching solo record. So, Tom and Harry are never seen in the temp table.

Thanks,
Brandon

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 01:12:08

here you go!


UPDATE t
SET t.GuestlistMon = STUFF((SELECT ',' + Guestname  FROM table WHERE ID = t.ID AND Name=t.Name AND GuestOn ='Mon' FOR XML PATH('')),1,1,''),
t.GuestlistTue = STUFF((SELECT ',' + Guestname  FROM table WHERE ID = t.ID AND Name=t.Name AND GuestOn ='Tue' FOR XML PATH('')),1,1,'')
FROM table t
CROSS APPLY (SELECT COUNT(1) AS Cnt
             FROM table
             WHERE ID = t.ID
             AND   Name = t.Name
             AND Guestname  > ''
            )t1
WHERE COALESCE(t.Guestname,'') = ''

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BJHennegan
Starting Member

6 Posts

Posted - 2012-04-19 : 12:35:12

Thanks so much.

Something is not quite right, I guess.

I copied the query and changed "table" to my actual table name. That should have been all I needed to do, yes? I don't get any errors, so it seemed right.

When I run this on the sample data I only see correct information for the first record (ie, Bob's GuestlistMon shows George, Mary, Sam. Cindy's GuestlistMon is still blank). To see if it had something to do with Mon vs Tue, I changed Cindy to "Mon", but same result. Only the Bob record is correct. I then changed both Bob and Cindy to Tue. Same result, only Bob has a GuestlistTue, Cindy's GuestlistTue is blank.

So, I'm also trying to learn what you've done here, and I understand most of it, but what does the "FOR XML PATH('')" do, exactly?

Appreciate it.

-Brandon

BJHennegan
Starting Member

6 Posts

Posted - 2012-04-19 : 12:59:35

I changed


AND GuestOn ='Mon'


AND (GuestOn ='Mon' OR GuestOn ='Both')

(and similarly for Tuesday) I changed the Bob-Sam record to 'Both' and it worked beautifully, Sam appeared in both lists and George and Mary in just one. Of course, it is still only working for the first record (as described in the post above).

-Brandon

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 14:20:05

[code]
UPDATE t
SET t.GuestlistMon = STUFF((SELECT ',' + Guestname FROM table WHERE ID = t.ID AND Name=t.Name AND GuestOn ='Mon' FOR XML PATH('')),1,1,''),
t.GuestlistTue = STUFF((SELECT ',' + Guestname FROM table WHERE ID = t.ID AND Name=t.Name AND GuestOn ='Tue' FOR XML PATH('')),1,1,'')
FROM table t
CROSS APPLY (SELECT COUNT(1) AS Cnt
FROM table
WHERE ID = t.ID
AND Name = t.Name
AND Guestname > ''
)t1
WHERE COALESCE(t.Guestname,'') = ''
AND Cnt >0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BJHennegan
Starting Member

6 Posts

Posted - 2012-04-19 : 14:44:53

no change in the result that I can see. Although do I get "(1 row(s) affected)" now instead of "(3 row(s) affected)"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 14:49:58

it will update only one row which is pending row for Cindy. So i guess it worked fine. Did you check table data now?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BJHennegan
Starting Member

6 Posts

Posted - 2012-04-19 : 15:12:07

Looks like I had a missing record in the sample data. Once I fixed that it seemed to work OK. I'm trying it out on real data now.

Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 15:23:33

ok...good luck

let me know if you face any issues

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources