Concatenate results from another table

Question

I have two tables

TableA has three fields
Id | FieldA | SomethingElse

TableB has three fields as well
Id | FieldA_FK | FieldB

We can join the tables on

TableA.FieldA = TableB.FieldA_FK

I would like to select values on both these tables in order to retrieve the following dataset:

TableA.Id, TableA.FieldA, TableA.SomethingElse, [Concatenation of TableB.FieldB]

To retrieve [Concatenation of TableB.FieldB], I know I can do

declare @result varchar(500);
set @result = '';
select @result = COALESCE(@result + ',', '')  + FieldB 
from TableB b 
join TableA a on a.FieldA = b.FieldA_FK
select @result

How can I get the result described above with the concatenation on one result row only?

Thanks in advance.

Examples of data:

TableA
1   A   something
2   B   somethingElse

TableB
1   A   Aa
2   A   Ab
3   A   Ac
4   B   Ba
5   B   Bb

I would like to retrieve

1   A   something       Aa, Ab, Ac
2   B   somethingElse   Ba, Bb

so `TableB` has multiple rows for the given `TableA.FieldA`? — cjb110, Jul 20 '15 at 13:59
Absolutely, it may have none to multiple rows for each FieldA. — G. Abitbol, Jul 20 '15 at 14:01
Why has `TableA` a primary-key `Id` but `TableB` references `TableB` via foreign-key to another column `FieldA` instead of the `Id`-column? — Tim Schmelter, Jul 20 '15 at 14:01
It is the way the database is designed. I must work with this. — G. Abitbol, Jul 20 '15 at 14:02
I see the answer in the "related" section, on the right side,http://stackoverflow.com/questions/8084997/how-to-implode-de-normalize-concat-multiple-columns-into-a-single-column?rq=1 — hazimdikenli, Jul 20 '15 at 14:11

Tim Schmelter · Accepted Answer · 2015-07-20T14:12:30.133

2

You can use the FOR XML PATH command:

SELECT 
    TableA.Id, TableA.FieldA, TableA.SomethingElse,
    [Concatenation of TableB.FieldB] =
    (STUFF((SELECT CAST(', ' + TableB.FieldB AS VARCHAR(MAX)) 
            FROM TableB
            WHERE (TableA.FieldA = TableB.FieldA_FK) 
            FOR XML PATH ('')), 1, 2, '')) 
FROM TableA

Demo

edited Jul 20 '15 at 14:12

answered Jul 20 '15 at 14:11

Tim Schmelter

450,073
74
686
939

Works great, Thanks for taking the time Tim, and thanks to you guys who also did. – G. Abitbol Jul 20 '15 at 14:21

score 1 · Answer 2 · answered Jul 20 '15 at 14:12

This isn't as obvious as it should be, but you can abuse SQL's XML methods:

select a.ID, a.FieldA, a.FieldB
, stuff(
   (select ', ' + TableB.FieldB
   from TableB 
   inner join TableA on TableB.TableA_FK = TableA.ID
   for xml path(''), type
   ).value('(./text())[1]', 'varchar(max)')
   , 1,2,'')
from TableA a

See this fiddle: http://sqlfiddle.com/#!3/0fdd52/5

Concatenate results from another table

2 Answers2