SQL Server에서 여러 행의 텍스트를 단일 텍스트 문자열로 연결하는 방법

질문자 :JohnnyM

세 개의 행이 있는 이름을 보유하는 데이터베이스 테이블을 고려하십시오.

 Peter Paul Mary

Peter, Paul, Mary 의 단일 문자열로 바꾸는 쉬운 방법이 있습니까?

SQL Server 2017 또는 Azure를 사용하는 경우 Mathieu Renda 답변을 참조하세요.

일대다 관계로 두 테이블을 조인하려고 할 때도 비슷한 문제가 있었습니다. XML PATH 메서드가 행 연결을 매우 쉽게 처리할 수 있음을 발견했습니다.

STUDENTS 라는 테이블이 있는 경우

 SubjectID StudentName ---------- ------------- 1 Mary 1 John 1 Sam 2 Alaina 2 Edward

내가 예상한 결과는 다음과 같습니다.

 SubjectID StudentName ---------- ------------- 1 Mary, John, Sam 2 Alaina, Edward

다음 T-SQL .

 SELECT Main.SubjectID, LEFT(Main.Students,Len(Main.Students)-1) As "Students" FROM ( SELECT DISTINCT ST2.SubjectID, ( SELECT ST1.StudentName + ',' AS [text()] FROM dbo.Students ST1 WHERE ST1.SubjectID = ST2.SubjectID ORDER BY ST1.SubjectID FOR XML PATH ('') ) [Students] FROM dbo.Students ST2 ) [Main]

substring 을 사용하여 첫 번째 항목을 건너뛸 수 있으면 더 간결한 방식으로 동일한 작업을 수행할 수 있으므로 하위 쿼리를 수행할 필요가 없습니다.

 SELECT DISTINCT ST2.SubjectID, SUBSTRING( ( SELECT ','+ST1.StudentName AS [text()] FROM dbo.Students ST1 WHERE ST1.SubjectID = ST2.SubjectID ORDER BY ST1.SubjectID FOR XML PATH ('') ), 2, 1000) [Students] FROM dbo.Students ST2

Ritesh

이 답변은 예기치 않은 결과 를 반환할 수 있습니다. 일관된 결과를 얻으려면 다른 답변에 자세히 설명된 FOR XML PATH 방법 중 하나를 사용하십시오.

COALESCE 사용:

 DECLARE @Names VARCHAR(8000) SELECT @Names = COALESCE(@Names + ', ', '') + Name FROM People

약간의 설명(이 답변은 상대적으로 규칙적인 견해를 얻는 것 같기 때문에):

• Coalesce는 실제로 다음 두 가지를 수행하는 유용한 치트입니다.

@Names 를 빈 문자열 값으로 초기화할 필요가 없습니다.

2) 끝부분에 여분의 분리막을 제거할 필요가 없습니다.

• 위의 솔루션은 행에 NULL 이름 값이 있는 경우 잘못된 결과를 제공 합니다(NULL 이 있는 경우 NULL 은 @Names NULL로 만들고 다음 행은 다시 빈 문자열로 시작합니다. 하나로 쉽게 수정 두 가지 솔루션 중:

 DECLARE @Names VARCHAR(8000) SELECT @Names = COALESCE(@Names + ', ', '') + Name FROM People WHERE Name IS NOT NULL

또는:

 DECLARE @Names VARCHAR(8000) SELECT @Names = COALESCE(@Names + ', ', '') + ISNULL(Name, 'N/A') FROM People

원하는 동작에 따라(첫 번째 옵션은 NULL을 필터링하기만 하고 두 번째 옵션은 표시 메시지와 함께 목록에 유지합니다['N/A'를 적절한 것으로 교체]).

Chris Shaffer

SQL Server 2017 이상 및 SQL Azure: STRING_AGG

SQL Server의 다음 버전부터는 변수나 XML 마법에 의존하지 않고도 행 전체를 연결할 수 있습니다.

STRING_AGG(Transact-SQL)

그룹화하지 않고

 SELECT STRING_AGG(Name, ', ') AS Departments FROM HumanResources.Department;

그룹화:

 SELECT GroupName, STRING_AGG(Name, ', ') AS Departments FROM HumanResources.Department GROUP BY GroupName;

그룹화 및 하위 정렬로

 SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS Departments FROM HumanResources.Department GROUP BY GroupName;

Mathieu Renda

XML data() 명령을 통해 아직 표시되지 않은 한 가지 방법은 다음과 같습니다.

FName이라는 열이 하나 있는 NameList라는 테이블을 가정합니다.

 SELECT FName + ', ' AS 'data()' FROM NameList FOR XML PATH('')

보고:

 "Peter, Paul, Mary, "

추가 쉼표만 처리해야 합니다.

@NReilingh의 의견에서 채택한 대로 다음 방법을 사용하여 후행 쉼표를 제거할 수 있습니다. 동일한 테이블 및 열 이름을 가정합니다.

 STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(FName)) AS 'data()' FROM NameList FOR XML PATH('')),' #!',', '), 1, 2, '') as Brands

jens frandsen

SQL 서버 2005에서

 SELECT Stuff( (SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE) .value('text()[1]','nvarchar(max)'),1,2,N'')

---

SQL 서버 2016에서

FOR JSON 구문을 사용할 수 있습니다

즉

 SELECT per.ID, Emails = JSON_VALUE( REPLACE( (SELECT _ = em.Email FROM Email em WHERE em.Person = per.ID FOR JSON PATH) ,'"},{"_":"',', '),'$[0]._' ) FROM Person per

그리고 그 결과는

 Id Emails 1 abc@gmail.com 2 NULL 3 def@gmail.com, xyz@gmail.com

데이터에 잘못된 XML 문자가 포함된 경우에도 작동합니다.

'"},{"_":"' 당신의 데이터가 포함 된 경우 때문에 안전하다 '"},{"_":"', 가 탈출한다 "},{\"_\":\"

', ' 를 임의의 문자열 구분 기호로 바꿀 수 있습니다.

---

그리고 SQL Server 2017에서는 Azure SQL Database

새로운 STRING_AGG 함수를 사용할 수 있습니다.

Community Wiki

MySQL에는 여러 행의 값을 연결할 수 있는 GROUP_CONCAT() 함수가 있습니다. 예시:

 SELECT 1 AS a, GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') AS people FROM users WHERE id IN (1,2,3) GROUP BY a

Darryl Hein

COALESCE 사용 - 여기에서 자세히 알아보기

예를 들어:

102

103

104

그런 다음 SQL Server에서 아래 코드를 작성하십시오.

 Declare @Numbers AS Nvarchar(MAX) -- It must not be MAX if you have few numbers SELECT @Numbers = COALESCE(@Numbers + ',', '') + Number FROM TableName where Number IS NOT NULL SELECT @Numbers

출력은 다음과 같습니다.

 102,103,104

pedram

PostgreSQL 어레이는 훌륭합니다. 예시:

몇 가지 테스트 데이터를 생성합니다.

 postgres=# \c test You are now connected to database "test" as user "hgimenez". test=# create table names (name text); CREATE TABLE test=# insert into names (name) values ('Peter'), ('Paul'), ('Mary'); INSERT 0 3 test=# select * from names; name ------- Peter Paul Mary (3 rows)

배열로 집계합니다.

 test=# select array_agg(name) from names; array_agg ------------------- {Peter,Paul,Mary} (1 row)

배열을 쉼표로 구분된 문자열로 변환합니다.

 test=# select array_to_string(array_agg(name), ', ') from names; array_to_string ------------------- Peter, Paul, Mary (1 row)

완료

PostgreSQL 9.0 이후로는 훨씬 더 쉽습니다 .

hgmnz

Oracle 11g 릴리스 2는 LISTAGG 기능을 지원합니다.여기 문서 .

 COLUMN employees FORMAT A50 SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees FROM emp GROUP BY deptno; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 ADAMS,FORD,JONES,SCOTT,SMITH 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD 3 rows selected.

경고

결과 문자열이 4000자를 초과할 가능성이 있는 경우 이 함수를 구현하는 데 주의하십시오. 예외가 발생합니다. 이 경우 예외를 처리하거나 결합된 문자열이 4000자를 초과하지 않도록 방지하는 자체 함수를 실행해야 합니다.

Alex

SQL Server 2005 이상에서는 아래 쿼리를 사용하여 행을 연결합니다.

 DECLARE @t table ( Id int, Name varchar(10) ) INSERT INTO @t SELECT 1,'a' UNION ALL SELECT 1,'b' UNION ALL SELECT 2,'c' UNION ALL SELECT 2,'d' SELECT ID, stuff( ( SELECT ','+ [Name] FROM @t WHERE Id = t.Id FOR XML PATH('') ),1,1,'') FROM (SELECT DISTINCT ID FROM @t ) t

Yogesh Bhadauirya

집에서 SQL Server에 액세스할 수 없으므로 여기에 있는 구문을 추측할 수 있지만 대략 다음과 같습니다.

 DECLARE @names VARCHAR(500) SELECT @names = @names + ' ' + Name FROM Names

Dana

재귀적 CTE 솔루션이 제안되었지만 코드가 제공되지 않았습니다. 아래 코드는 재귀적 CTE의 예입니다.

결과가 질문과 일치하더라도 데이터는 주어진 설명과 완전히 일치하지 않습니다. 테이블의 모든 행이 아니라 행 그룹에서 실제로 이 작업을 수행하기를 원한다고 가정하기 때문입니다. 테이블의 모든 행과 일치하도록 변경하는 것은 독자의 연습 문제로 남아 있습니다.

 ;WITH basetable AS ( SELECT id, CAST(name AS VARCHAR(MAX)) name, ROW_NUMBER() OVER (Partition BY id ORDER BY seq) rw, COUNT(*) OVER (Partition BY id) recs FROM (VALUES (1, 'Johnny', 1), (1, 'M', 2), (2, 'Bill', 1), (2, 'S.', 4), (2, 'Preston', 5), (2, 'Esq.', 6), (3, 'Ted', 1), (3, 'Theodore', 2), (3, 'Logan', 3), (4, 'Peter', 1), (4, 'Paul', 2), (4, 'Mary', 3) ) g (id, name, seq) ), rCTE AS ( SELECT recs, id, name, rw FROM basetable WHERE rw = 1 UNION ALL SELECT b.recs, r.ID, r.name +', '+ b.name name, r.rw + 1 FROM basetable b INNER JOIN rCTE r ON b.id = r.id AND b.rw = r.rw + 1 ) SELECT name FROM rCTE WHERE recs = rw AND ID=4

jmoreno

최종 결과를 보유하고 선택할 변수를 생성해야 합니다.

가장 쉬운 솔루션

 DECLARE @char VARCHAR(MAX); SELECT @char = COALESCE(@char + ', ' + [column], [column]) FROM [table]; PRINT @char;

Tigerjz32

SQL Server vNext에서는 STRING_AGG 함수로 기본 제공됩니다. STRING_AGG(Transact-SQL) 에서 이에 대해 자세히 알아보세요.

Henrik Fransas

XML을 사용하면 행을 쉼표로 구분하는 데 도움이 되었습니다. 추가 쉼표의 경우 SQL Server의 바꾸기 기능을 사용할 수 있습니다. 쉼표를 추가하는 대신 AS 'data()'를 사용하면 행이 공백으로 연결되며 나중에 아래에 작성된 구문과 같이 쉼표로 대체될 수 있습니다.

 REPLACE( (select FName AS 'data()' from NameList for xml path('')) , ' ', ', ')

Diwakar

추가 쉼표 없이 바로 사용할 수 있는 솔루션:

 select substring( (select ', '+Name AS 'data()' from Names for xml path('')) ,3, 255) as "MyList"

빈 목록은 NULL 값이 됩니다. 일반적으로 목록을 테이블 열이나 프로그램 변수에 삽입합니다. 필요에 따라 최대 길이를 255로 조정합니다.

(Diwakar와 Jens Frandsen은 좋은 답변을 제공했지만 개선이 필요합니다.)

Daniel Reis

SELECT STUFF((SELECT ', ' + name FROM [table] FOR XML PATH('')), 1, 2, '')

다음은 샘플입니다.

 DECLARE @t TABLE (name VARCHAR(10)) INSERT INTO @t VALUES ('Peter'), ('Paul'), ('Mary') SELECT STUFF((SELECT ', ' + name FROM @t FOR XML PATH('')), 1, 2, '') --Peter, Paul, Mary

Max Szczurek

이것은 나를 위해 일했습니다 ( SQL Server 2016 ).

 SELECT CarNamesString = STUFF(( SELECT ',' + [Name] FROM tbl_cars FOR XML PATH('') ), 1, 1, '')

출처: https://www.mytecbits.com/

그리고 MySQL 용 솔루션(이 페이지는 MySQL용 Google에 표시되기 때문에):

 SELECT [Name], GROUP_CONCAT(DISTINCT [Name] SEPARATOR ',') FROM tbl_cars

MySQL 문서에서 .

Arash.Zandi

SQL Server 2017 이상 버전에서는 STRING_AGG() 함수를 사용하여 쉼표로 구분된 값을 생성할 수 있습니다. 아래에서 한 가지 예를 살펴보십시오.

 SELECT VendorId,STRING_AGG(FirstName,',') UsersName FROM Users where VendorId!=9 GROUP BY VendorId

sameer Ahmed

다른 답변의 경우 답변을 읽는 사람은 차량 또는 학생과 같은 특정 도메인 테이블을 알고 있어야 합니다. 솔루션을 테스트하려면 테이블을 생성하고 데이터로 채워야 합니다.

다음은 SQL Server "Information_Schema.Columns" 테이블을 사용하는 예입니다. 이 솔루션을 사용하면 테이블을 생성하거나 데이터를 추가할 필요가 없습니다. 이 예에서는 데이터베이스의 모든 테이블에 대해 쉼표로 구분된 열 이름 목록을 만듭니다.

 SELECT Table_Name ,STUFF(( SELECT ',' + Column_Name FROM INFORMATION_SCHEMA.Columns Columns WHERE Tables.Table_Name = Columns.Table_Name ORDER BY Column_Name FOR XML PATH ('')), 1, 1, '' )Columns FROM INFORMATION_SCHEMA.Columns Tables GROUP BY TABLE_NAME

Mike Barlow - BarDev

DECLARE @Names VARCHAR(8000) SELECT @name = '' SELECT @Names = @Names + ',' + Names FROM People SELECT SUBSTRING(2, @Names, 7998)

이렇게 하면 시작 부분에 쉼표가 표시됩니다.

그러나 다른 열이 필요하거나 하위 테이블을 CSV로 작성하려면 이를 스칼라 UDF(사용자 정의 필드)로 래핑해야 합니다.

XML 경로를 SELECT 절에서 상관 하위 쿼리로 사용할 수도 있습니다(하지만 Google은 집에서 작업을 수행하지 않기 때문에 다시 작업할 때까지 기다려야 합니다 :-)

gbn

Oracle DB의 경우 저장 프로시저를 생성하지 않고 Oracle에서 여러 행을 하나로 연결하는 방법은 무엇입니까?를 참조하십시오.

가장 좋은 대답은 Oracle 11g 릴리스 2 이상에서 사용할 수 있는 내장 LISTAGG() 함수를 사용하는 @Emmanuel이 한 것으로 보입니다.

 SELECT question_id, LISTAGG(element_id, ',') WITHIN GROUP (ORDER BY element_id) FROM YOUR_TABLE; GROUP BY question_id

@ user762952가 지적했듯이 Oracle 문서 http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php 에 따르면 WM_CONCAT() 함수도 옵션입니다. 안정적인 것처럼 보이지만 Oracle은 모든 애플리케이션 SQL에 사용하지 말 것을 명시적으로 권장하므로 사용에 대한 위험은 사용자가 감수해야 합니다.

그 외에는 자신의 함수를 작성해야 합니다. 위의 Oracle 문서에는 이를 수행하는 방법에 대한 가이드가 있습니다.

ZeroK

null 값을 방지하려면 CONCAT()을 사용할 수 있습니다.

 DECLARE @names VARCHAR(500) SELECT @names = CONCAT(@names, ' ', name) FROM Names select @names

Rapunzo

나는 Dana의 대답의 우아함이 정말 좋았고 그것을 완성하고 싶었습니다.

 DECLARE @names VARCHAR(MAX) SET @names = '' SELECT @names = @names + ', ' + Name FROM Names -- Deleting last two symbols (', ') SET @sSql = LEFT(@sSql, LEN(@sSql) - 1)

Community Wiki

MySQL 전체 예:

많은 데이터를 가질 수 있는 사용자가 있고 목록에서 모든 사용자의 데이터를 볼 수 있는 출력을 원합니다.

결과:

 ___________________________ | id | rowList | |-------------------------| | 0 | 6, 9 | | 1 | 1,2,3,4,5,7,8,1 | |_________________________|

테이블 설정:

 CREATE TABLE `Data` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1; INSERT INTO `Data` (`id`, `user_id`) VALUES (1, 1), (2, 1), (3, 1), (4, 1), (5, 1), (6, 0), (7, 1), (8, 1), (9, 0), (10, 1); CREATE TABLE `User` ( `id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `User` (`id`) VALUES (0), (1);

질문:

 SELECT User.id, GROUP_CONCAT(Data.id ORDER BY Data.id) AS rowList FROM User LEFT JOIN Data ON User.id = Data.user_id GROUP BY User.id

user1767754

이 답변을 사용하려면 서버에 대한 일부 권한이 필요합니다.

어셈블리 는 당신에게 좋은 선택입니다. 만드는 방법을 설명하는 사이트가 많이 있습니다. 나는 아주 잘 설명되어 생각하는 일이있다 하나 .

원하는 경우 이미 어셈블리를 생성했으며 여기 에서 DLL 파일을 다운로드할 수 있습니다.

다운로드한 후에는 SQL Server에서 다음 스크립트를 실행해야 합니다.

 EXEC sp_configure 'show advanced options', 1 RECONFIGURE; EXEC sp_configure 'clr strict security', 1; RECONFIGURE; CREATE Assembly concat_assembly AUTHORIZATION dbo FROM '<PATH TO Concat.dll IN SERVER>' WITH PERMISSION_SET = SAFE; GO CREATE AGGREGATE dbo.concat ( @Value NVARCHAR(MAX) , @Delimiter NVARCHAR(4000) ) RETURNS NVARCHAR(MAX) EXTERNAL Name concat_assembly.[Concat.Concat]; GO sp_configure 'clr enabled', 1; RECONFIGURE

어셈블리 경로가 서버에 액세스할 수 있는지 확인합니다. 모든 단계를 성공적으로 완료했으므로 다음과 같은 기능을 사용할 수 있습니다.

 SELECT dbo.Concat(field1, ',') FROM Table1

---

SQL Server 2017 부터 STRING_AGG 함수를 사용할 수 있습니다.

Nizam

나는 일반적으로 SQL Server에서 문자열을 연결하기 위해 다음과 같이 선택을 사용합니다.

 with lines as ( select row_number() over(order by id) id, -- id is a line id line -- line of text. from source -- line source ), result_lines as ( select id, cast(line as nvarchar(max)) line from lines where id = 1 union all select l.id, cast(r.line + N', ' + l.line as nvarchar(max)) from lines l inner join result_lines r on l.id = r.id + 1 ) select top 1 line from result_lines order by id desc

Vladimir Nesterovsky

null을 처리하려면 where 절을 추가하거나 첫 번째 절 주위에 다른 COALESCE를 추가하여 처리할 수 있습니다.

 DECLARE @Names VARCHAR(8000) SELECT @Names = COALESCE(COALESCE(@Names + ', ', '') + Name, @Names) FROM People

Pramod

Chris Shaffer의 답변 외에도 :

다음과 같이 데이터가 반복될 수 있는 경우

 Tom Ali John Ali Tom Mike

Tom,Ali,John,Ali,Tom,Mike

DISTINCT를 사용하여 중복을 피하고 Tom,Ali,John,Mike 얻을 수 있습니다.

 DECLARE @Names VARCHAR(8000) SELECT DISTINCT @Names = COALESCE(@Names + ',', '') + Name FROM People WHERE Name IS NOT NULL SELECT @Names

asmgx

Oracle에서는 wm_concat 입니다. 이 기능은 10g 릴리스 이상에서 사용할 수 있다고 생각합니다.

user762952

출처 : http:www.stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv