将相同值的行内容进行合并操作--Sql2005
如何将相同键值的蓝位内容值串接 ?
举例来说 TableA 如下:
ID Type DESC
1 cpu 处理器
1 cpu 双核心
1 cpu 800外频
2 HD 硬盘
2 HD 500G
2 HD 5400转
希望将字段内容串接并以【,】分隔,在 SQL Server 2005 可以利用 XML PATH 来将功能实作,如:
ID Type DESC
1 cpu 处理器,双核心,800外频
2 HD 硬盘,500G,5400转
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
SELECT T1.id,
T1.type,
( STUFF(( SELECT ',' + [DESC]
FROM @TABLEA T2
WHERE T2.id = T1.id
FOR
XML PATH('')
), 1, 1, '') ) AS [DESC]
FROM @TABLEA T1
GROUP BY id,
type
如果只是想单纯地将字段内容单纯串接,则可以用下列做法:
ID Type DESC
1 cpu 处理器双核心800外频
2 HD 硬盘500G5400转
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
SELECT T1.id,
T1.type,
( SELECT [DESC] + ''
FROM @TABLEA T2
WHERE T2.id = T1.id
FOR
XML PATH('')
) AS [DESC]
FROM @TABLEA T1
GROUP BY id,
type
如果是 SQL Server 2000 则请参考 Concatenate the values in a column in SQL Server 2000 and 2005 文章
关于 PATH 与 STUFF 用法则参考: