asked on

SQL Update column with multiple entries

I have 1 table with a unique reference and 1 line per unique reference (code)
I have another table with multiple records (somewhere between zero and 10) with the same code (unique reference in the first table).
I want to bring in the multiple records from the 2nd table into the 1st table, but I want to bring them in in one column separated by a comma and space
Example below

1st table
code      person      Qty      Time        Clothes

11111      Steve      1      10:00
22222      Ray        2      11:00
33333      Alex       3      12:00

2nd table
code      clothes

11111      Hat
11111      Coat
33333      Hat
33333      Coat
33333      Gloves


1st table after stored procedure
code      person      Qty      Time       Clothes

11111      Steve      1      10:00      Hat, Coat
22222      Ray       2      11:00
33333      Alex        3      12:00      Hat, Coat, Gloves

Can anyone offer some advice?

Thanks

SOLUTION

Bhavesh Shah

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Barry Cunney

Hi IT-Misfit,
First let us know why it is required to bring the records from the second table into a single column in the first table, because we may then be able to advise on a better approach than this.

Thanks

ASKER CERTIFIED SOLUTION

Rajkumar Gs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

IT-Misfit

ASKER

Thanks all

Raj I think you have cracked it, with a little tweaking using your code I think i can get what i need

Many Thanks

IT-Misfit

ASKER

Raj,
Your code gets me the results i need but I then need to populate table one with the result column of hat, coat, gloves etc .
I have tried a few ways of doing this but my SQL skills are not strong so could you help?
Thanks

Bhavesh Shah

Hi,

Check out this full code.

Declare @Aliases Table
(
        [FirstName] varchar(50) not null,
        [LastName] varchar(50) not null,
        [Alias] varchar(100) not null,
        [function] varchar(100) null
)

Insert @Aliases
Select 'Clark','Kent','Superman',null
Union All
Select 'Peter','Parker','Spiderman',null

Declare @Function Table
(
        [FirstName] varchar(50) not null,
        [Function] varchar(100) null
)

Insert @Function
Select 'Clark','asp'
Union All
Select 'Clark','cfm'
Union All
Select 'Clark','jsp'
UNION ALL
Select 'Peter','vb'
Union All
Select 'Peter','vc'


update @Aliases 
set [function] = b.FUNCLIST
from @Aliases A INNER JOIN (Select Distinct
								[FirstName],
								(Stuff((Select ', ' + [function]From @Function T2 Where T2.FirstName = T1.FirstName FOR XML PATH('')),1,2,'')) as FUNCLIST
						From @Aliases T1
					
						)B
ON A.FIRSTNAME=B.FIRSTNAME						

SELECT * FROM @Aliases

Open in new window