[sql] SQL Server에서 여러 행의 텍스트를 단일 텍스트 문자열로 연결하는 방법은 무엇입니까?

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

Peter
Paul
Mary

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



답변

SQL Server 2017 또는 Azure를 사용하는 경우 Mathieu Renda answer를 참조하십시오 .

일대 다 관계로 두 테이블을 조인하려고 할 때 비슷한 문제가 발생했습니다. SQL 2005에서 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


답변

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

사용 COALESCE:

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

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

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

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

2) 끝에서 여분의 분리기를 떼어 낼 필요가 없습니다.

  • 행이있는 경우이 솔루션은 위의 잘못된 결과를 얻을 수 NULL 가있는 경우 (이름 값을 NULLNULL을 만들 것입니다 @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’를 적절한 것으로 바꿉니다).


답변

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;


답변

XML data()MS SQL Server에서 명령을 통해 아직 표시되지 않은 방법 은 다음과 같습니다.

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


답변

에서 SQL Server 2005의

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

SQL Server 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 기능을 사용할 수 있습니다


답변

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


답변

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

예를 들어 :

102

103

104

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

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