Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

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

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
wademi
Asked:
wademi
1 Solution
 
Ephraim WangoyaCommented:
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
 
BlakeRogersCommented:
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
 
SharathData EngineerCommented:
What is your SQL Server version?
0
 
wademiAuthor Commented:
Th version is SQL Server 2008
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now