Search code examples
sqlconcatenationfor-xml

Concatenating data in sql

Given the following SQL tables (with data)

create table a (key1 INT, value1 NVARCHAR(MAX))
create table b (fk INT, value2 NVARCHAR(MAX))

INSERT INTO a(key1, value1) values (1, 'test1')
INSERT INTO a(key1, value1) values (2, 'test2')

INSERT INTO b(fk, value2) values (1, 'b1.1')
INSERT INTO b(fk, value2) values (1, 'b1.2')
INSERT INTO b(fk, value2) values (1, 'b1.3')

I would like to return the foreign key values in b concatenated together like so:

key1    value1  col
1       test1   b1.1, b1.2, b1.3
2       test2   NULL

I know it's possible (theoretically) with FOR XML PATH, I just can't figure it out

Solution

  • This should get you the results you want:

    select a.key1,
  a.value1,
  stuff((select distinct ', ' + value2
         from b
         where a.key1 = b.fk 
         for XML path('')),1,1,'') 
from a

    See SQL Fiddle with Demo