query to join two tables using a primary key and comma separate the results

Question

Hi, Suppose there are two tables-

T1                  T2
---             ----------
A                A  |   1
A                A  |   2
A                A  |   3

How to write a query that returns the results as following

Col1    Col2
----    -----
  A     1,2,3

(One row)

Thanks in advance

Answer 1

Declare @tempA Table
(Val Char(1))

DECLARE @tempB Table
(Val Char(1),
 ID INT)

 INSERT INTO @tempA
 SELECT 'A'
 UNION 
 SELECT 'A'
 UNION 
 SELECT 'A'

 INSERT INTO @tempB
 SELECT 'A',1
 UNION 
 SELECT 'A',2
 UNION 
 SELECT 'A',3


 SELECT DISTINCT Val,STUFF((SELECT ','+CONVERT(VARCHAR,ID)as 'data()'
              FROM @tempB B
              Where B.Val = A.Val
              FOR XML PATH('') ),1,1,'')
FROM @tempA A

Hope this will solve.