Solved

SQL Update column with multiple entries

Posted on 2010-09-16
6
385 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
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now