Updating records that share a common Identifier with multiple values 2

[lsgtechuser]

Sorry if the title is a little confusing, I have a table that has multiple people per household with a household identifier. I'm trying to update a column in the table with all of the names from a given household concatinated together for mailing labels.

the table layout is as follows
UniquID, HHID, Fname, Lastname,.. ToBeUpdW/allNamesinHH

what would be the easiest way to do this? I'm sure it's much easier than I think it is.

 

[lsgtechuser]

To add more...
Some sample data would be
UniquID, HHID, Fname, Lastname,.. ToBeUpdW/allNamesinHH
=======================================================
0001 1000 John Doe Null
0002 1000 Jane Doe Null
0003 1000 Bill Doe Null

I want to have it look like this in the end
UniquID, HHID, Fname, Lastname,.. ToBeUpdW/allNamesinHH
=======================================================
0001 1000 John Doe John, Jane, Bill
0002 1000 Jane Doe John, Jane, Bill
0003 1000 Bill Doe John, Jane, Bill

 

[rac2]

Here is a technique that you might be able to use.

/*Move values from many rows into a string of comma separated values in one row.*/

DECLARE @s VARCHAR(1000)
DECLARE @h INT
SET @h = 1000
select @s = coalesce(@s+',','') + Fname from MyTable
				 WHERE HHID = @h
 
SELECT @h, @s

 

[lsgtechuser]

Thanks a lot, that helped. if i wanted to do this with all of the different HHIDs in the table and make one long list, how would i do this? if i create a cursor it would just run each statement seperately no?

 

[jbenson001]

Try this using rac2's example:

declare @t TABLE (UniqueID varchar(10), HHID varchar(10), FName varchar(50), LName varchar(50), AllNames varchar(100))

Insert into @t
     values ('0001','1000','John','Doe', NULL)
Insert into @t
     values ('0002','1000','Jane','Doe', NULL)
Insert into @t
     values ('0003','1000','Bill','Doe', NULL)
Insert into @t
     values ('0004','2000','Steve','Smith', NULL)
Insert into @t
     values ('0005','2000','Joe','Smith', NULL)
Insert into @t
     values ('0006','2000','Jim','Smith', NULL)


declare @HHID varchar(10)

Select Distinct(HHID)
Into #IDs
From @t

Set @HHID = (Select MIN(HHID) From @t)

While @HHID <= (Select MAX(HHID) From #IDs)
Begin
   DECLARE @s VARCHAR(1000)
   select @s = coalesce(@s+',','') + Fname from @t
                    WHERE HHID = @HHID

   Update @t
   Set AllNames = @s Where HHID = @HHID


   Delete From #IDs Where HHID = @HHID
   Set @HHID = (Select MIN(HHID) From #IDs)
   Set @s = NULL
   
End
 
select * from @t
drop table #IDs

Jim