wake-up-neo.com

SQL group_concat-Funktion in SQL Server

Wenn es eine Tabelle gibt, die Mitarbeiter heißt

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

Ergebnis, das ich in diesem Format brauche: 

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

F: Dieser Datensatz befindet sich in derselben Employee-Tabelle. Ich habe fast keine Erfahrung mit der Verwendung von UDFs, gespeicherten Prozeduren, ich muss diese Sache durch Abfrage erledigen.

sql-server-2008asp-classic
22
Gurvinder
  1. FÜR XML-PFAD trick und article
  2. CLR Benutzerdefiniertes Aggregat
  3. für SQL Server vor Version 2005 - temporäre Tabellen

Ein Beispiel für # 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

Das abschließende Komma entfernen - liegt allein

A CLR-Aggregat c # -Code für # 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
    }
}
31
Oleg Dok

Die von @ OlegDoks gewählte Antwort gibt möglicherweise das korrekte Ergebnis zurück. Aber die Leistung kann schrecklich sein. Dieses Testszenario veranschaulicht es.

Erstellung einer temporären Tabelle:

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

Dies sind nur 10.000 Zeilen. Aber viele identische EmpId.

Diese Abfrage in Olegs Antwort dauerte 64 Sekunden in meiner Datenbank.

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

In dieser Situation ist es nicht die richtige Art, Reihen zu säubern. Um diesen kartesischen Join zu vermeiden, reduzieren Sie die anfängliche Anzahl der IDs, bevor Sie dem Join beitreten.

Dies ist die richtige Art und Weise, dies zu handhaben:

;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

Dies dauert weniger als 1 Sekunde

13
t-clausen.dk

Ich denke, es gibt keine GROUP_CONCAT-Funktion in MSSQL. Dieser Artikel zeigt verschiedene Möglichkeiten, Zeilenwerte zu konaktieren.

Verkettung von Werten, wenn die Anzahl der Elemente klein ist und bereits bekannt ist

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 ;

Weitere Wege auf diesem Link.

0
John Woo

Dies ist die Lösung für das am Anfang gegebene Beispiel:

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
0
Moez Guedri