以下用法是将多行 某一列的值,按照另外的某一个为基础,合并为以逗号隔开;
table1表字段:operationNameID,resourceID
值:
1,2
1,3
1,4
2,2
2,4
table2表字段:resourceID,resourceName
值:
2,xx2
3,xx3
4,xx4
table3表字段:operationNameID
值:
1
2
3
SELECT table3.operationNameID
,(SELECT name = (
(
stuff(
(
SELECT
‘,’ + CAST (b.resourceName AS VARCHAR)
FROM
table1 a
JOIN table2 b ON a.resourceID = b.resourceID
WHERE a.operationNameID = table3.operationNameID
ORDER BY
a.operationNameID FOR xml path (’’)
),
1, 1, ‘’
)
)
)
) AS resourceName
FROM table3
查询结果:
1 xxx2,xx3,xx4
2 xx2,xx4