sub query with comma delimited output in one column

Question

I need help with writing a query where the query output displays a column with comma delimited data.

I have two tables with one to many relationship. I want to write a query which outputs all records from table with one record but display comma delimited data in one column from the table having many records.

Table1

Col1 Col2 Col3
a1     1   4
a2     2   5
a3     3   6
a4     7   8

Table2

Col1 Col4
a1     10
a1     11
a1     22
a2     10
a2     11
a3     19
a3     22
a3     23
a4     15

Query output:

Col1 Col2 Col3 Col4
a1     1   4    10,11,22
a2     2   5    10,11
a3     3   6    19,22,23
a4     7   8    15

Thanks in advance

Answer 1

You can use the following:

select t1.col1,
  t1.col2, 
  t1.col3,
  left(t2.col4, len(t2.col4)-1) col4
from table1 t1
cross apply
(
  select cast(t2.Col4 as varchar(10)) + ', '
  from Table2 t2
  where t1.col1 = t2.col1
  FOR XML PATH('')
) t2 (col4)

See SQL Fiddle with Demo.

Or you can use:

select t1.col1,
  t1.col2, 
  t1.col3,
  STUFF(
         (SELECT ', ' + cast(t2.Col4 as varchar(10))
          FROM Table2 t2
          where t1.col1 = t2.col1
          FOR XML PATH (''))
          , 1, 1, '')  AS col4
from table1 t1

See SQL Fiddle with Demo

Answer 2

SELECT  Col1, 
        Col2, 
        Col3,
        Col4 = SUBSTRING((
                            SELECT ',' + af.awardFocusName
                              FROM Table2 t2    
                             WHERE t2.Col1 = t1.Col1
                            FOR XML PATH('')
                        ), 2, 1000000)
  FROM  Table1 t1