我如何连接并使一组文本在sql服务器? [英] How can i concatenate and make a group of text in sql server?
本文介绍了我如何连接并使一组文本在sql服务器?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
可能重复:
HEre的另一个版本
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
Possible Duplicate:
Simulating group_concat MySQL function in MS SQL Server 2005?I have a table tb1. I want concatenated result set.
Please help me by writing a query for this problem?
解决方案Here, try this one,
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 Demo
HEre's another version
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 Demo
这篇关于我如何连接并使一组文本在sql服务器?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文