SQL group_concat function in SQL Server [duplicate

问题:

This question already has an answer here:

• Simulating group_concat MySQL function in Microsoft SQL Server 2005? 9 answers

If there is a table called employee

EmpID           EmpName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I need in this format:

EmpID           EmpName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

Q: this record is in same Employee table. I have almost no experience using UDFs, stored procedures, I need to be done this thing through query.Is this possible without using UDFs, SP\'s.

回答1:

1. FOR XML PATH trick and article
2. CLR User defined aggregate
3. for sql server prior version 2005 - temporary tables

An example of #1

DECLARE @t TABLE (EmpId INT, EmpName VARCHAR(100))
INSERT @t VALUES
(1, \'Mary\'),(1, \'John\'),(1, \'Sam\'),(2, \'Alaina\'),(2, \'Edward\')
SELECT distinct
    EmpId,
    (
        SELECT EmpName+\',\'
        FROM @t t2
        WHERE t2.EmpId = t1.EmpId
        FOR XML PATH(\'\')
    ) Concatenated
FROM @t t1

How to strip the final comma - is on your own

A CLR aggregate c# code for #2

using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;
using System.IO;

namespace DatabaseAssembly
{
    [Serializable]
    [SqlUserDefinedAggregate(Format.UserDefined,
        IsInvariantToNulls = true,
        IsInvariantToDuplicates = true,
        IsInvariantToOrder = true,
        MaxByteSize = -1)]
    public struct StringJoin : IBinarySerialize
    {
        private Dictionary<string, string> AggregationList
        {
            get
            {
                if (_list == null)
                    _list = new Dictionary<string, string>();
                return _list;
            }
        }
        private Dictionary<string, string> _list;

        public void Init()
        {

        }

        public void Accumulate(SqlString Value)
        {
            if (!Value.IsNull)
                AggregationList[Value.Value.ToLowerInvariant()] = Value.Value;

        }

        public void Merge(StringJoin Group)
        {
            foreach (var key in Group.AggregationList.Keys)
                AggregationList[key] = Group.AggregationList[key];
        }

        public SqlChars Terminate()
        {
            var sb = new StringBuilder();
            foreach (var value in AggregationList.Values)
                sb.Append(value);
            return new SqlChars(sb.ToString());
        }

        #region IBinarySerialize Members

        public void Read(System.IO.BinaryReader r)
        {

            try
            {
                while (true)
                    AggregationList[r.ReadString()] = r.ReadString();
            }
            catch (EndOfStreamException)
            {

            }
        }

        public void Write(System.IO.BinaryWriter w)
        {
            foreach (var key in AggregationList.Keys)
            {
                w.Write(key);
                w.Write(AggregationList[key]);
            }
        }

        #endregion
    }
}

回答2:

The chosen answer from @OlegDok\'s may return the correct result. But the performance can be terrible. This test scenario will illustrate it.

Creation of a temp table:

CREATE table #temp (EmpId INT, EmpName VARCHAR(100))
;WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a,N b,N c,N d,N e,N f)
INSERT #temp
SELECT EmpId, EmpName FROM (values(1, \'Mary\'),(1, \'John\'),(1, \'Sam\')) x(EmpId, EmpName)
CROSS APPLY 
(SELECT top 2000 N FROM tally) y
UNION ALL
SELECT EmpId, EmpName FROM (values(2, \'Alaina\'),(2, \'Edward\')) x(EmpId, EmpName)
CROSS APPLY
(SELECT top 2000 N FROM tally) y

This is only 10.000 rows. But lots of identical EmpId.

This query in Oleg\'s answer took 64 seconds on my database.

SELECT distinct
    EmpId,
    (
        SELECT EmpName+\',\'
        FROM #temp t2
        WHERE t2.EmpId = t1.EmpId
        FOR XML PATH(\'\')
    ) Concatenated
FROM #temp t1

Distinct is not the correct way of cleaning up rows in this situation. To avoid this cartesian join, reduce the initial number of IDs before joining like this.

This is the correct way of handling this:

;WITH CTE as
(
  SELECT distinct EmpId
  FROM #temp
)
SELECT 
    EmpId,
    STUFF((
        SELECT \',\'+EmpName
        FROM #temp t2
        WHERE t2.EmpId = t1.EmpId
        FOR XML PATH(\'\')
    ), 1,1,\'\') Concatenated
FROM CTE t1

This takes less than 1 second

回答3:

I think there is no GROUP_CONCAT function in MSSQL. This article shows different ways of concactenating row values.

Concatenating values when the number of items is small and known upfront

SELECT CategoryId,
       MAX( CASE seq WHEN 1 THEN ProductName ELSE \'\' END ) + \', \' +
       MAX( CASE seq WHEN 2 THEN ProductName ELSE \'\' END ) + \', \' +
       MAX( CASE seq WHEN 3 THEN ProductName ELSE \'\' END ) + \', \' +
       MAX( CASE seq WHEN 4 THEN ProductName ELSE \'\' END )
  FROM ( SELECT p1.CategoryId, p1.ProductName,
                ( SELECT COUNT(*) 
                    FROM Northwind.dbo.Products p2
                   WHERE p2.CategoryId = p1.CategoryId
                     AND p2.ProductName <= p1.ProductName )
           FROM Northwind.dbo.Products p1 ) D ( CategoryId, ProductName, seq )
 GROUP BY CategoryId ;

More ways on this link.

回答4:

This is the solution for the example given in the beginning:

SELECT DISTINCT emp_name,
STUFF(
(SELECT \', \' + RTRIM(proj_id)
FROM project_members AS t1 
WHERE t1.emp_name = t2.emp_name
FOR XML PATH (\'\'))
, 1, 1, \'\')
FROM project_members t2