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.
精彩评论