sql-server-2008 - SQL Server 中的 SQL group_concat 函数

Question

这个问题在这里已经有了答案:





Simulating group_concat MySQL function in Microsoft SQL Server 2005?

(12 个回答)


4年前关闭。




如果有一张表叫employee

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

结果我需要这种格式:

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

问:这条记录在同一个 Employee table 。我几乎没有使用 UDF、存储过程的经验，我需要通过查询来完成这件事。这可能不使用 UDF、SP。

Answer 1

对于 XML 路径 trick和 article

CLR 用户定义聚合

对于 2005 年以前版本的 sql server - 临时表

#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

如何去除最后一个逗号 - 由您自己决定

#2 的 CLR 聚合 c# 代码

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
    }
}