可能的重复:
在 MS SQL Server 2005 中模拟 group_concat MySQL 函数? https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005
我有一张桌子tb1。我想要串联的结果集。
请帮我写一个关于这个问题的查询?
来,试试这个,
SELECT a.dept_id,
NewTable.NameValues
FROM (
SELECT DISTINCT dept_ID
FROM tableA
) a
LEFT JOIN
(
SELECT dept_id,
STUFF((
SELECT ', ' + [Name]
FROM tableA
WHERE ( dept_id = Results.dept_id )
FOR XML PATH('')), 1, 1, '') AS NameValues
FROM tableA Results
GROUP BY dept_id
) NewTable
on a.dept_id = NewTable.dept_id
GO
SQLFiddle 演示 http://sqlfiddle.com/#!3/0d102/5
这是另一个版本
SELECT a.dept_id,
SUBSTRING(d.nameList,1, LEN(d.nameList) - 1) ConcatenateNames
FROM
(
SELECT DISTINCT dept_id
FROM tableA
) a
CROSS APPLY
(
SELECT name + ', '
FROM tableA AS B
WHERE A.dept_id = B.dept_id
FOR XML PATH('')
) D (nameList)
GO
SQLFiddle 演示 http://sqlfiddle.com/#!3/0d102/10
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)