Solved

SQL Update column with multiple entries

Posted on 2010-09-16
6
390 Views
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?

Thanks
0
Comment
Question by:IT-Misfit
[X]
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
6 Comments
 
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
        [LastName],
        [FirstName],
        (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

0
 
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.

Thanks

0
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 400 total points
ID: 33690568
Try this code
Raj
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

0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:IT-Misfit
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
0
 

Author Comment

by:IT-Misfit
ID: 33691641
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
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33691750
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

0

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

719 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