我如何连接并使一组文本在sql服务器？ [英] How can i concatenate and make a group of text in sql server?

问题描述

可能重复：

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屋！