Microsoft SQL Server 2005에서 group_concat MySQL 함수를 시뮬레이션하고 있습니까?

Microsoft SQL Server 2005에서 group_concat MySQL 함수를 시뮬레이션하고 있습니까?

MySQL 기반 앱을 Microsoft SQL Server 2005로 마이그레이션하려고 합니다(선택한 것이 아니라 이것이 생명입니다).

원래 앱에서는 거의 모든 ANSI-SQL 준거 스테이트먼트를 사용했습니다만, 중요한 예외는 MySQL을 사용했습니다.group_concat꽤 자주 기능합니다.

group_concat덧붙여서, 예를 들어 종업원 이름이나 프로젝트 표가 주어지면...

SELECT empName, projID FROM project_members;

반환:

ANDY   |  A100
ANDY   |  B391
ANDY   |  X010
TOM    |  A100
TOM    |  A510

group_concat을 통해 얻을 수 있는 것은 다음과 같습니다.

SELECT 
    empName, group_concat(projID SEPARATOR ' / ') 
FROM 
    project_members 
GROUP BY 
    empName;

반환:

ANDY   |  A100 / B391 / X010
TOM    |  A100 / A510

그래서 내가 알고 싶은 것은:예를 들어, SQL Server에서 사용자 정의 함수를 작성하여 다음 기능을 에뮬레이트할 수 있습니까?group_concat?

UDF나 스토어드 프로시저 등은 스트레이트업 SQL을 사용한 경험이 거의 없기 때문에 설명을 너무 많이 해 주세요.

정말 쉬운 방법은 없습니다.많은 아이디어들이 있지만요

내가 찾은 최고의 것:

SELECT table_name, LEFT(column_names , LEN(column_names )-1) AS column_names
FROM information_schema.columns AS extern
CROSS APPLY
(
    SELECT column_name + ','
    FROM information_schema.columns AS intern
    WHERE extern.table_name = intern.table_name
    FOR XML PATH('')
) pre_trimmed (column_names)
GROUP BY table_name, column_names;

또는 데이터에 다음과 같은 문자가 포함된 경우 올바르게 작동하는 버전입니다.<

WITH extern
     AS (SELECT DISTINCT table_name
         FROM   INFORMATION_SCHEMA.COLUMNS)
SELECT table_name,
       LEFT(y.column_names, LEN(y.column_names) - 1) AS column_names
FROM   extern
       CROSS APPLY (SELECT column_name + ','
                    FROM   INFORMATION_SCHEMA.COLUMNS AS intern
                    WHERE  extern.table_name = intern.table_name
                    FOR XML PATH(''), TYPE) x (column_names)
       CROSS APPLY (SELECT x.column_names.value('.', 'NVARCHAR(MAX)')) y(column_names) 

파티에 조금 늦을 수도 있지만, 이 방법은 나에게 효과적이고 COALESCE 방식보다 쉽습니다.

SELECT STUFF(
             (SELECT ',' + Column_Name 
              FROM Table_Name
              FOR XML PATH (''))
             , 1, 1, '')

SQL Server 2017은 새로운 집약 기능을 도입했습니다.

를 클릭합니다STRING_AGG ( expression, separator).

문자열 표현식의 값을 연결하고 구분자 값을 그 사이에 배치합니다.구분 기호는 문자열 끝에 추가되지 않습니다.

연결된 요소는 다음을 추가하여 정렬할 수 있습니다.WITHIN GROUP (ORDER BY some_expression)

버전 2005-2016에서는 일반적으로 XML 방식을 수락된 답변에 사용합니다.

그러나 일부 상황에서는 실패할 수 있습니다. 예를 들어 연결할 데이터가 다음을 포함하는 경우CHAR(29)보다시피

XML에서 허용되지 않는 문자(0x001D)가 포함되어 있으므로 XML에서 데이터를 직렬화할 수 없습니다.

모든 문자를 처리할 수 있는 보다 견고한 방법은 CLR 집약을 사용하는 것입니다.그러나 이 접근법에서는 연결된 요소에 순서를 적용하는 것이 더 어렵다.

변수에 할당하는 방법은 보장되지 않으므로 프로덕션 코드에서는 피해야 합니다.

지금 당장 도움이 되기에는 너무 늦었을지도 모르지만, 이것이 일을 하는 가장 쉬운 방법이 아닐까요?

SELECT     empName, projIDs = replace
                          ((SELECT Surname AS [data()]
                              FROM project_members
                              WHERE  empName = a.empName
                              ORDER BY empName FOR xml path('')), ' ', REQUIRED SEPERATOR)
FROM         project_members a
WHERE     empName IS NOT NULL
GROUP BY empName

Github의 GROUP_CONCAT 프로젝트를 보세요.저는 당신이 찾고 있는 것을 하고 있다고 생각합니다.

이 프로젝트에는 MySQL GROUP_CONCAT 함수와 유사한 기능을 일괄적으로 제공하는 SQLCLR UDA(사용자 정의 집계 함수) 세트가 포함되어 있습니다.필요한 기능을 기반으로 최적의 성능을 보장하기 위해 여러 가지 기능이 있습니다.

여러 프로젝트 관리자가 있는 프로젝트의 모든 프로젝트 관리자 이름을 연결하려면 다음과 같이 입력합니다.

SELECT a.project_id,a.project_name,Stuff((SELECT N'/ ' + first_name + ', '+last_name FROM projects_v 
where a.project_id=project_id
 FOR
 XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N''
) mgr_names
from projects_v a
group by a.project_id,a.project_name

.PermissionLevel=External전 코드로 곳에서 보안 등).ALTER DATABASE database_name SET TRUSTWORTHY ON.

using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.IO;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
using Microsoft.SqlServer.Server;

[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined,
    MaxByteSize=8000,
    IsInvariantToDuplicates=true,
    IsInvariantToNulls=true,
    IsInvariantToOrder=true,
    IsNullIfEmpty=true)]
public struct CommaDelimit : IBinarySerialize
{
    [Serializable]
    private class StringList : List<string>
    { }

    private StringList List;

    public void Init()
    {
        this.List = new StringList();
    }

    public void Accumulate(SqlString value)
    {
        if (!value.IsNull)
            this.Add(value.Value);
    }

    private void Add(string value)
    {
        if (!this.List.Contains(value))
            this.List.Add(value);
    }

    public void Merge(CommaDelimit group)
    {
        foreach (string s in group.List)
        {
            this.Add(s);
        }
    }

    void IBinarySerialize.Read(BinaryReader reader)
    {
        IFormatter formatter = new BinaryFormatter();
        this.List = (StringList)formatter.Deserialize(reader.BaseStream);
    }

    public SqlString Terminate()
    {
        if (this.List.Count == 0)
            return SqlString.Null;

        const string Separator = ", ";

        this.List.Sort();
 
        return new SqlString(String.Join(Separator, this.List.ToArray()));
   }

   void IBinarySerialize.Write(BinaryWriter writer)
   {
        IFormatter formatter = new BinaryFormatter();
        formatter.Serialize(writer.BaseStream, this.List);
    }
}

다음과 같은 쿼리를 사용하여 테스트했습니다.

SELECT 
 dbo.CommaDelimit(X.value) [delimited] 
FROM 
 (
  SELECT 'D' [value] 
  UNION ALL SELECT 'B' [value] 
  UNION ALL SELECT 'B' [value] -- intentional duplicate
  UNION ALL SELECT 'A' [value] 
  UNION ALL SELECT 'C' [value] 
 ) X 

산출량: A, B, C, D

MS SQL Server 2005에서 이것을 사용해 보았습니다만, 다음의 것이 가장 도움이 되었습니다.이것은 xaprb에서 찾을 수 있었습니다.

declare @result varchar(8000);

set @result = '';

select @result = @result + name + ' '

from master.dbo.systypes;

select rtrim(@result);

@말씀하신대로 스페이스 캐릭터로 인해 문제가 생겼습니다.

J Hardiman의 답변에 대해 어떻게 생각하십니까?

SELECT empName, projIDs=
  REPLACE(
    REPLACE(
      (SELECT REPLACE(projID, ' ', '-somebody-puts-microsoft-out-of-his-misery-please-') AS [data()] FROM project_members WHERE empName=a.empName FOR XML PATH('')), 
      ' ', 
      ' / '), 
    '-somebody-puts-microsoft-out-of-his-misery-please-',
    ' ') 
  FROM project_members a WHERE empName IS NOT NULL GROUP BY empName

덧붙여서, "Surname"을 사용하는 것은 오타입니까, 아니면 개념을 이해하지 못하는 것입니까?

어쨌든, 여러분, 감사합니다.시간이 꽤 절약되었습니다:)

2021

@AbdusSalamAzad의 답은 정답이다.

SELECT STRING_AGG(my_col, ',') AS my_result FROM my_tbl;

결과가 너무 크면 STRING_AGG 집약결과가 제한치인 8000바이트를 초과했습니다. LOB 타입을 사용하여 결과 절단을 회피합니다." 쿼리를 다음과 같이 변경하여 수정할 수 있습니다.

SELECT STRING_AGG(convert(varchar(max), my_col), ',') AS my_result FROM my_tbl;

구글 동료 여러분, 여기 간단한 플러그 앤 플레이 솔루션이 있습니다.이러한 솔루션은 한동안 복잡한 솔루션과 씨름하다가 도움이 되었습니다.

SELECT
distinct empName,
NewColumnName=STUFF((SELECT ','+ CONVERT(VARCHAR(10), projID ) 
                     FROM returns 
                     WHERE empName=t.empName FOR XML PATH('')) , 1 , 1 , '' )
FROM 
returns t

ID를 문자열로 연결하려면 ID를 VARCHAR로 변환해야 합니다.그렇게 할 필요가 없다면 더 간단한 버전을 다음에 제시하겠습니다.

SELECT
distinct empName,
NewColumnName=STUFF((SELECT ','+ projID
                     FROM returns 
                     WHERE empName=t.empName FOR XML PATH('')) , 1 , 1 , '' )
FROM 
returns t

이에 대한 모든 공적은 https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9508abc2-46e7-4186-b57f-7f368374e084/replicating-groupconcat-function-of-mysql-in-sql-server?forum=transactsql에 있습니다.

SQL Server 2017+의 경우 STRING_AGG() 함수를 사용합니다.

    SELECT STRING_AGG(Genre, ',') AS Result
    FROM Genres;

샘플 결과:

결과

록, 재즈, 컨트리, 팝, 블루스, 힙합, 랩, 펑크

언급URL : https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005