Solved

SQL query to create multiple values in a field when joining more than one table

Posted on 2011-03-16
4
255 Views
Last Modified: 2012-05-11
I need help to add mulitple values to a field in a  select query
I have joined 2 tables.

Table A

tableA_ID    nameid    Role
1                   1             1
1                  2             2
2                  3            1
2                  4            1
3                  5            1
3                  6            2
3                   7         3

Table B has values with foreign key(ID) from A like

tableA_ID Nameid realname
1                  1     john
1                    2     harry
3                    3     paul
3                  4     Dave
3                  5      Brooks


I need to create a select query joining table A with table B but I need a column in the results that joins realnames where the tableA_ID in Table B are the same.

So the results would show look like

tableA_ID   realname
1             john,harry
3             paul,dave,brooks
0
Comment
Question by:wademi
4 Comments
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 35152427
try
SELECT TableA_ID, Stuff(
                  (Select  ',' + RealName
                              From    TableB B
                              Where   A.TableA_ID = B.TableA_ID
                              For xml Path('')
                  )
                        
                  , 1, 1, '') as [Real Names]
                  
                        
From    TableA A
Group By TableA_ID

Open in new window

0
 

Expert Comment

by:BlakeRogers
ID: 35152457
This would work too I believe
SELECT TA.TableA_ID, TB.RealName
FROM TableA AS TA INNER JOIN TableB AS TB ON TA.TableA_ID = TB.TableA_ID
Group BY TA.TableA_ID

Open in new window

0
 
LVL 40

Expert Comment

by:Sharath
ID: 35153271
What is your SQL Server version?
0
 

Author Comment

by:wademi
ID: 35153532
Th version is SQL Server 2008
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL for Frequently Bought With 11 45
T-SQL for SS2000 -- get characters until a digit 6 19
Permissions on Database 11 36
Need to update TableA to TableB 6 33
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

914 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

16 Experts available now in Live!

Get 1:1 Help Now