首先看一下数据结构表
1 IF(OBJECT_ID('tempdb..#tProduct')IS NOT NULL) 2 DROP TABLE #tProduct 3 SELECT * INTO #tProduct FROM( 4 SELECT 1 AS CatagoryID,1 AS ProductID,'aaa'AS ProductName 5 UNION 6 SELECT 1 AS CatagoryID,2 AS ProductID,'bbb'AS ProductName 7 UNION 8 SELECT 1 AS CatagoryID,3 AS ProductID,'ccc'AS ProductName 9 UNION 10 SELECT 2 AS CatagoryID,4 AS ProductID,'xxx'AS ProductName 11 UNION 12 SELECT 2 AS CatagoryID,5 AS ProductID,'yyy'AS ProductName 13 UNION 14 SELECT 2 AS CatagoryID,6 AS ProductID,'zzz'AS ProductName 15 )TEMP 16 --SELECT * FROM #tProduct
所需的查询结果
|
CatagoryID |
Products |
|
1 |
aaa,bbb,ccc |
|
2 |
xxx,yyy,zzz |
查询语句实现
1 SELECT DISTINCT T1.CatagoryID 2 ,Products=REPLACE((SELECT ProductNameAS [data()] FROM #tProduct T2 WHERE T2.CatagoryID=T1.CatagoryID ORDER BY ProductID FOR XML PATH('')),' ',',') 3 FROM #tProduct T1 ORDER BY CatagoryID
讲解：请依次执行以下语句查看结果
更多关于FOR XML PATH 请参考msdn。
1、使用FOR XML PATH生成XML文档字符串
1 SELECT CatagoryID,ProductID,ProductName FROM #tProduct 2 FOR XML PATH('Products'),ROOT('root')
简化一下
1 SELECT ProductName FROM #tProduct 2 FOR XML PATH('')
2、使用data()去掉XML标签元素(这样就使得一列的值构成一个以空格为分隔符的字符串)
1 SELECT ProductName AS [data()] FROM #tProduct 2 FOR XML PATH('')
说明：通过使用 data() 作为列名， SELECT 语句将返回 ProductName 的列表。又由于FOR XML PATH(‘’) 指定了一个空字符串作为行元素名，因此不会生成元素
3、见最终的实现语句