GROUP BY를 사용하여 SQL Server에서 문자열을 연결하는 방법은 무엇입니까?

GROUP BY를 사용하여 SQL Server에서 문자열을 연결하는 방법은 무엇입니까?

---

어떻게 내가 가질까:

id       Name       Value
1          A          4
1          B          8
2          C          9

에

id          Column
1          A:4, B:8
2          C:9

---

CURSOR, WHILE 루프 또는 사용자 정의 함수가 필요하지 않습니다 .

FOR XML과 PATH로 창의력을 발휘해야합니다.

[참고 :이 솔루션은 SQL 2005 이상에서만 작동합니다. 원래 질문은 사용중인 버전을 지정하지 않았습니다.]

CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)

SELECT 
  [ID],
  STUFF((
    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) 
    FROM #YourTable 
    WHERE (ID = Results.ID) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID

DROP TABLE #YourTable

---

SQL Server 2017 또는 SQL Server Vnext, SQL Azure 인 경우 아래와 같이 string_agg를 사용할 수 있습니다.

select id, string_agg(concat(name, ':', [value]), ', ')
    from #YourTable 
    group by id

---

XML 경로를 사용하면 예상대로 완벽하게 연결되지 않습니다. "&"를 "& amp;"로 바꿉니다. 또한 <" and ">몇 가지 다른 일이있을 수 있습니다.

이에 대한 해결 방법을 발견했습니다 ... 교체해야합니다.

FOR XML PATH('')
)

와:

FOR XML PATH(''),TYPE
).value('(./text())[1]','VARCHAR(MAX)')

... 또는 NVARCHAR(MAX)그것이 당신이 사용하는 것이라면.

도대체 SQL에 결합 집계 함수 가없는 이유는 무엇입니까? 이것은 PITA입니다.

---

내가 문자열에 공백 및 특수 XML 문자 (포함하여 작업에 케빈 페어차일드의 제안을 변환하려고 할 때 나는 몇 가지 문제로 실행 &, <, >인코딩 된).

내 코드의 최종 버전 (원래 질문에 대답하지는 않지만 누군가에게 유용 할 수 있음)은 다음과 같습니다.

CREATE TABLE #YourTable ([ID] INT, [Name] VARCHAR(MAX), [Value] INT)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'Oranges & Lemons',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'1 < 2',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)

SELECT  [ID],
  STUFF((
    SELECT ', ' + CAST([Name] AS VARCHAR(MAX))
    FROM #YourTable WHERE (ID = Results.ID) 
    FOR XML PATH(''),TYPE 
     /* Use .value to uncomment XML entities e.g. &gt; &lt; etc*/
    ).value('.','VARCHAR(MAX)') 
  ,1,2,'') as NameValues
FROM    #YourTable Results
GROUP BY ID

DROP TABLE #YourTable

공백을 구분 기호로 사용하고 모든 공백을 쉼표로 바꾸는 대신 각 값 앞에 쉼표와 공백을 추가 한 다음 STUFF처음 두 문자를 제거하는 데 사용 합니다.

XML 인코딩은 TYPE 지시문 을 사용하여 자동으로 처리됩니다 .

---

Sql Server 2005 이상을 사용하는 다른 옵션

---- test data
declare @t table (OUTPUTID int, SCHME varchar(10), DESCR varchar(10))
insert @t select 1125439       ,'CKT','Approved'
insert @t select 1125439       ,'RENO','Approved'
insert @t select 1134691       ,'CKT','Approved'
insert @t select 1134691       ,'RENO','Approved'
insert @t select 1134691       ,'pn','Approved'

---- actual query
;with cte(outputid,combined,rn)
as
(
  select outputid, SCHME + ' ('+DESCR+')', rn=ROW_NUMBER() over (PARTITION by outputid order by schme, descr)
  from @t
)
,cte2(outputid,finalstatus,rn)
as
(
select OUTPUTID, convert(varchar(max),combined), 1 from cte where rn=1
union all
select cte2.outputid, convert(varchar(max),cte2.finalstatus+', '+cte.combined), cte2.rn+1
from cte2
inner join cte on cte.OUTPUTID = cte2.outputid and cte.rn=cte2.rn+1
)
select outputid, MAX(finalstatus) from cte2 group by outputid

---

http://groupconcat.codeplex.com 에서 SQLCLR 집계를 설치하십시오.

그런 다음 요청한 결과를 얻기 위해 다음과 같은 코드를 작성할 수 있습니다.

CREATE TABLE foo
(
 id INT,
 name CHAR(1),
 Value CHAR(1)
);

INSERT  INTO dbo.foo
    (id, name, Value)
VALUES  (1, 'A', '4'),
        (1, 'B', '8'),
        (2, 'C', '9');

SELECT  id,
    dbo.GROUP_CONCAT(name + ':' + Value) AS [Column]
FROM    dbo.foo
GROUP BY id;

---

SQL Server 2005 이상에서는 연결 등을 포함하여 사용자 지정 집계 함수 를 만들 수 있습니다 . 링크 된 기사의 맨 아래에있는 샘플을 참조하십시오.

---

예를 들면

Oracle에서는 LISTAGG 집계 함수를 사용할 수 있습니다.

원본 기록

name   type
------------
name1  type1
name2  type2
name2  type3

SQL

SELECT name, LISTAGG(type, '; ') WITHIN GROUP(ORDER BY name)
FROM table
GROUP BY name

의 결과

name   type
------------
name1  type1
name2  type2; type3

---

8 년 후 ... Microsoft SQL Server vNext 데이터베이스 엔진은 그룹화 된 문자열 연결을 직접 지원하도록 Transact-SQL을 향상 시켰습니다. Community Technical Preview 버전 1.0은 STRING_AGG 함수를 추가했으며 CTP 1.1은 STRING_AGG 함수에 WITHIN GROUP 절을 추가했습니다.

참조 : https://msdn.microsoft.com/en-us/library/mt775028.aspx

---

이러한 종류의 질문은 여기에서 매우 자주 요청되며 솔루션은 기본 요구 사항에 크게 의존합니다.

https://stackoverflow.com/search?q=sql+pivot

과

https://stackoverflow.com/search?q=sql+concatenate

일반적으로 동적 SQL, 사용자 정의 함수 또는 커서가 없으면 SQL 전용 방법이 없습니다.

---

이것은 Kevin Fairchild의 게시물에 추가 된 것입니다 (매우 영리합니다). 나는 그것을 의견으로 추가했을 것이지만, 아직 충분한 지적이 없다. :)

나는이 아이디어를 내가 일하고있는 견해에 사용했지만 연결하는 항목에는 공백이 포함되었습니다. 그래서 공백을 구분 기호로 사용하지 않도록 코드를 약간 수정했습니다.

멋진 해결 방법 Kevin에게 다시 한 번 감사드립니다!

CREATE TABLE #YourTable ( [ID] INT, [Name] CHAR(1), [Value] INT ) 

INSERT INTO #YourTable ([ID], [Name], [Value]) VALUES (1, 'A', 4) 
INSERT INTO #YourTable ([ID], [Name], [Value]) VALUES (1, 'B', 8) 
INSERT INTO #YourTable ([ID], [Name], [Value]) VALUES (2, 'C', 9) 

SELECT [ID], 
       REPLACE(REPLACE(REPLACE(
                          (SELECT [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) as A 
                           FROM   #YourTable 
                           WHERE  ( ID = Results.ID ) 
                           FOR XML PATH (''))
                        , '</A><A>', ', ')
                ,'<A>','')
        ,'</A>','') AS NameValues 
FROM   #YourTable Results 
GROUP  BY ID 

DROP TABLE #YourTable 

---

Cade가 말한 것에 덧붙이 기 위해, 이것은 일반적으로 프론트 엔드 디스플레이 일이므로 처리해야합니다. 파일 내보내기 나 다른 "SQL 전용"솔루션과 같은 경우 SQL로 100 %를 작성하는 것이 더 쉽다는 것을 알고 있지만,이 연결은 대부분 디스플레이 계층에서 처리해야합니다.

---

커서가 필요하지 않습니다 ... while 루프는 충분합니다.

------------------------------
-- Setup
------------------------------

DECLARE @Source TABLE
(
  id int,
  Name varchar(30),
  Value int
)

DECLARE @Target TABLE
(
  id int,
  Result varchar(max) 
)


INSERT INTO @Source(id, Name, Value) SELECT 1, 'A', 4
INSERT INTO @Source(id, Name, Value) SELECT 1, 'B', 8
INSERT INTO @Source(id, Name, Value) SELECT 2, 'C', 9


------------------------------
-- Technique
------------------------------

INSERT INTO @Target (id)
SELECT id
FROM @Source
GROUP BY id

DECLARE @id int, @Result varchar(max)
SET @id = (SELECT MIN(id) FROM @Target)

WHILE @id is not null
BEGIN
  SET @Result = null

  SELECT @Result =
    CASE
      WHEN @Result is null
      THEN ''
      ELSE @Result + ', '
    END + s.Name + ':' + convert(varchar(30),s.Value)
  FROM @Source s
  WHERE id = @id

  UPDATE @Target
  SET Result = @Result
  WHERE id = @id

  SET @id = (SELECT MIN(id) FROM @Target WHERE @id < id)
END

SELECT *
FROM @Target

---

매우 간단하게합시다 :

SELECT stuff(
    (
    select ', ' + x from (SELECT 'xxx' x union select 'yyyy') tb 
    FOR XML PATH('')
    )
, 1, 2, '')

이 줄을 바꾸십시오 :

select ', ' + x from (SELECT 'xxx' x union select 'yyyy') tb

당신의 질문으로.

---

교차 적용 답변을 보지 못했으며 XML 추출이 필요하지 않습니다. Kevin Fairchild가 작성한 것과 약간 다른 버전이 있습니다. 보다 복잡한 쿼리에서 사용하는 것이 더 빠르고 쉽습니다.

   select T.ID
,MAX(X.cl) NameValues
 from #YourTable T
 CROSS APPLY 
 (select STUFF((
    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX))
    FROM #YourTable 
    WHERE (ID = T.ID) 
    FOR XML PATH(''))
  ,1,2,'')  [cl]) X
  GROUP BY T.ID

---

그룹별로 대부분 하나의 항목을 포함하는 경우 다음과 같은 방식으로 성능을 크게 향상시킬 수 있습니다.

SELECT 
  [ID],

CASE WHEN MAX( [Name]) = MIN( [Name]) THEN 
MAX( [Name]) NameValues
ELSE

  STUFF((
    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) 
    FROM #YourTable 
    WHERE (ID = Results.ID) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS NameValues

END

FROM #YourTable Results
GROUP BY ID

---

바꾸기 기능 및 FOR JSON PATH 사용

SELECT T3.DEPT, REPLACE(REPLACE(T3.ENAME,'{"ENAME":"',''),'"}','') AS ENAME_LIST
FROM (
 SELECT DEPT, (SELECT ENAME AS [ENAME]
        FROM EMPLOYEE T2
        WHERE T2.DEPT=T1.DEPT
        FOR JSON PATH,WITHOUT_ARRAY_WRAPPER) ENAME
    FROM EMPLOYEE T1
    GROUP BY DEPT) T3

샘플 데이터 및 기타 방법은 여기를 클릭하십시오

참고 URL : https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server