SQL Update column with multiple entries

Posted on 2010-09-16
Last Modified: 2012-05-10

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?

Question by:IT-Misfit
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 100 total points
ID: 33690497
check out this code.
you will get the idea.....
Declare @Aliases Table
        [FirstName] varchar(50) not null,
        [LastName] varchar(50) not null,
        [Alias] varchar(100) not null

Insert @Aliases
Select 'Clark','Kent','Superman'
Union All
Select 'Clark','Kent','Kal-El'
Union All
Select 'Clark','Kent','Gangbuster'
Union All
Select 'Clark','Kent','Supernova'
Union All
Select 'Clark','Kent','Nightwing'
Union All
Select 'Peter','Parker','Spiderman'
Union all
Select 'Peter','Parker','WebSligner'

Select * from @Aliases

Select Distinct
        (Stuff((Select ', ' + Alias From @Aliases T2 Where T2.FirstName = T1.FirstName and T2.LastName = T2.LastName FOR XML PATH('')),1,2,'')) as Aliases
From @Aliases T1
Order By [LastName], [FirstName]

Open in new window

LVL 17

Expert Comment

by:Barry Cunney
ID: 33690511
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.


LVL 23

Accepted Solution

Rajkumar Gs earned 400 total points
ID: 33690568
Try this code
SELECT DISTINCT A.code, person, Qty, Time
, Stuff((Select ', ' + clothes From YourTable2 Where code = A.code FOR XML PATH('')),1,2,'')
FROM YourTable1 A
	INNER JOIN YourTable2 B ON A.code = B.code

Open in new window

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.


Author Comment

ID: 33691178
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

Author Comment

ID: 33691641
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?
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33691750

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'
Select 'Peter','vb'
Union All
Select 'Peter','vc'

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

SELECT * FROM @Aliases

Open in new window


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL query for highest sequence 4 64
unable to reinstall SCVMM 2012 R2 - SQL errors. 5 42
sql query 5 44
SQL Server In place upgrade from 2012 to 2014 12 22
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question