세 개의 행이있는 이름을 보유한 데이터베이스 테이블을 고려하십시오.
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 가있는 경우 (이름 값을 NULL 은 NULL을 만들 것입니다
@NamesNULL을 해당 행 후, 다음 행은 다시 빈 문자열로 시작됩니다. 쉽게 두 가지 중 하나를 고정 솔루션 :
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 마녀에 의존하지 않고도 행 전체를 연결할 수 있습니다.
그룹화하지 않고
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