开发者

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

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


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜