복붙노트

[SQL] 2005 마이크로 소프트 SQL 서버에서 GROUP_CONCAT MySQL의 기능을 시뮬레이션?

SQL

2005 마이크로 소프트 SQL 서버에서 GROUP_CONCAT MySQL의 기능을 시뮬레이션?

나는 마이크로 소프트 SQL 서버 2005 (안 선택에 의해,하지만의 삶)을 통해 MySQL을 기반 응용 프로그램을 마이그레이션하는 것을 시도하고있다.

원래 응용 프로그램에서 우리는 하나 개의 중요한 제외하고는 거의 대부분 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 스트레이트 업이 없다 그래서 잘못을하십시오 너무 많은 설명의 측면에 :)

해결법

  1. ==============================

    1.진짜 쉬운 방법은이 작업을 수행 없습니다. 하지만 거기 아이디어를 많이.

    진짜 쉬운 방법은이 작업을 수행 없습니다. 하지만 거기 아이디어를 많이.

    내가 찾은 최고의 하나 :

    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)
  2. ==============================

    2.나는 조금 늦게 파티에있을 수 있지만,이 방법은 나를 위해 작동과 유착 방법보다 쉽습니다.

    나는 조금 늦게 파티에있을 수 있지만,이 방법은 나를 위해 작동과 유착 방법보다 쉽습니다.

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

    3.아마도 너무 늦게 지금 도움이 될,하지만이 일을 할 수있는 가장 쉬운 방법이 아니다합니까?

    아마도 너무 늦게 지금 도움이 될,하지만이 일을 할 수있는 가장 쉬운 방법이 아니다합니까?

    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
  4. ==============================

    4.SQL 서버 2017는 새로운 집계 함수를 소개하지

    SQL 서버 2017는 새로운 집계 함수를 소개하지

    STRING_AGG (식 분리기).

    연접 된 요소는 그룹 내에서 추가로 주문할 수 있습니다 (some_expression BY ORDER)

    버전 2005-2016를 들어 나는 일반적으로 허용 대답에서 XML 메소드를 사용합니다.

    그러나 이것은 경우에 따라 실패 할 수 있습니다. 예를 들면 연결될 수있는 데이터는 당신이 볼 (29) CHAR 포함 된 경우

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

    변수에 할당하는 방법은 보장되지 않으므로 생산 코드에 피해야한다.

  5. ==============================

    5.Github에서의 GROUP_CONCAT 프로젝트에서보세요, 난 당신이 찾고 정확히 않습니다 생각 :

    Github에서의 GROUP_CONCAT 프로젝트에서보세요, 난 당신이 찾고 정확히 않습니다 생각 :

  6. ==============================

    6.여러 프로젝트 관리자 쓰기가 프로젝트의 모든 프로젝트 매니저 이름을 연결하려면 :

    여러 프로젝트 관리자 쓰기가 프로젝트의 모든 프로젝트 매니저 이름을 연결하려면 :

    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
  7. ==============================

    7.코드 아래로 당신은 "ALTER DATABASE 데이터베이스 _ 세트를 배포하기 전에 프로젝트 속성에 PermissionLevel = 외부를 설정하고 실행하여 (반드시 보안 위험 및 [인증서와 같은] 대안에 대해 다른 곳에서 읽을 수) 외부 코드를 신뢰하도록 데이터베이스를 변경해야 TRUSTWORTHY ON ".

    코드 아래로 당신은 "ALTER DATABASE 데이터베이스 _ 세트를 배포하기 전에 프로젝트 속성에 PermissionLevel = 외부를 설정하고 실행하여 (반드시 보안 위험 및 [인증서와 같은] 대안에 대해 다른 곳에서 읽을 수) 외부 코드를 신뢰하도록 데이터베이스를 변경해야 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

  8. ==============================

    8.이러한 시도했지만 MS SQL Server 2005의 내 목적을 위해 다음은 내가 xaprb에서 발견되는 가장 유용했다

    이러한 시도했지만 MS SQL Server 2005의 내 목적을 위해 다음은 내가 xaprb에서 발견되는 가장 유용했다

    declare @result varchar(8000);

set @result = '';

select @result = @result + name + ' '

from master.dbo.systypes;

select rtrim(@result);

    당신이 나를 위해 문제가 발생하는 공백 문자였다 언급 한 바와 같이 @ 마크.

  9. ==============================

    9.J Hardiman의 대답에 대해, 방법에 대한 :

    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

    그런데, "성"오타의 사용 또는 여기 개념을 이해하고 있지 않다?

    어쨌든, 덕분에 많이들은 나에게 꽤 많은 시간을 절약 사촌 :)

  10. ==============================

    10.거기 내 동료 Google 직원, 여기에 잠시 동안 더 복잡한 솔루션과 사투를 벌인 후 나를 위해 일한 아주 간단한 플러그 앤 플레이 솔루션입니다 :

    거기 내 동료 Google 직원, 여기에 잠시 동안 더 복잡한 솔루션과 사투를 벌인 후 나를 위해 일한 아주 간단한 플러그 앤 플레이 솔루션입니다 :

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

    내가 문자열로을 연결하기 위해 VARCHAR로 ID를 전환했다는 것을 알 수 있습니다. 당신이 할 필요가없는 경우, 여기에 더 간단한 버전입니다 :

    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

    11. from https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 어떻게 SQL 서버에서 하나의 텍스트 문자열에 여러 행의 텍스트를 연결하는?  (0) 2020.03.04
[SQL] 각 그룹의 마지막 레코드를 가져 - MySQL의  (0) 2020.03.04
[SQL] 각 GROUP BY 그룹에서 첫 번째 행을 선택?  (0) 2020.03.04
[SQL] 열에 최대 값 행만에서 SQL [중복]  (0) 2020.03.04
[SQL] 때 MySQL의에서 작은 따옴표, 큰 따옴표, 및 역 따옴표를 사용하는  (0) 2020.03.04
,

티스토리툴바