[sql] 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 문자 (포함하여 작업에 케빈 페어차일드의 제안을 변환하려고 할 때 나는 몇 가지 문제로 실행 &, <, >인코딩 된).

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

a
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처음 두 문자를 제거하는 데 사용 합니다.

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


답변

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


댓글 달기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다