MSSQL
[MSSQL] 조회된 여러 행의 데이터를 1줄의 문자열로 출력
csmkong
2020. 7. 2. 11:51
SELECT 문을 실행하였을 때 여러행으로 출력되는 데이터를 1줄의 문자열로 묶을 필요가 있다.
문자열로 만들 때 각 행의 데이터를 구분자로 구분하여 출력할 수 있다.
데이터 : 배 A, B의 승무원이 다음과 같이 있다.
SELECT *
FROM (
SELECT 'A' NM_SHIP, '김바둑' NM_CREW UNION ALL
SELECT 'A' NM_SHIP, '강누렁' NM_CREW UNION ALL
SELECT 'A' NM_SHIP, '박흰둥' NM_CREW UNION ALL
SELECT 'A' NM_SHIP, '장까미' NM_CREW UNION ALL
SELECT 'A' NM_SHIP, '곽얼룩' NM_CREW UNION ALL
SELECT 'B' NM_SHIP, '이나비' NM_CREW UNION ALL
SELECT 'B' NM_SHIP, '고치즈' NM_CREW UNION ALL
SELECT 'B' NM_SHIP, '황삼색' NM_CREW UNION ALL
SELECT 'B' NM_SHIP, '신고등' NM_CREW UNION ALL
SELECT 'B' NM_SHIP, '임꼬물' NM_CREW
) A

요구 조건 : 배 A의 승무원을 한 줄의 문자열로 확인하고 싶다.
SELECT ''+A.NM_CREW
FROM (
SELECT 'A' NM_SHIP, '김바둑' NM_CREW UNION ALL
SELECT 'A' NM_SHIP, '강누렁' NM_CREW UNION ALL
SELECT 'A' NM_SHIP, '박흰둥' NM_CREW UNION ALL
SELECT 'A' NM_SHIP, '장까미' NM_CREW UNION ALL
SELECT 'A' NM_SHIP, '곽얼룩' NM_CREW UNION ALL
SELECT 'B' NM_SHIP, '이나비' NM_CREW UNION ALL
SELECT 'B' NM_SHIP, '고치즈' NM_CREW UNION ALL
SELECT 'B' NM_SHIP, '황삼색' NM_CREW UNION ALL
SELECT 'B' NM_SHIP, '신고등' NM_CREW UNION ALL
SELECT 'B' NM_SHIP, '임꼬물' NM_CREW
) A
WHERE A.NM_SHIP = 'A'
FOR XML PATH('')

추가 요구 조건 : 이름 사이에 ',' 로 구분하고 싶다.
SELECT ','+A.NM_CREW -- '' 공백문자를 ',' 쉼표로 변경
FROM (
SELECT 'A' NM_SHIP, '김바둑' NM_CREW UNION ALL
SELECT 'A' NM_SHIP, '강누렁' NM_CREW UNION ALL
SELECT 'A' NM_SHIP, '박흰둥' NM_CREW UNION ALL
SELECT 'A' NM_SHIP, '장까미' NM_CREW UNION ALL
SELECT 'A' NM_SHIP, '곽얼룩' NM_CREW UNION ALL
SELECT 'B' NM_SHIP, '이나비' NM_CREW UNION ALL
SELECT 'B' NM_SHIP, '고치즈' NM_CREW UNION ALL
SELECT 'B' NM_SHIP, '황삼색' NM_CREW UNION ALL
SELECT 'B' NM_SHIP, '신고등' NM_CREW UNION ALL
SELECT 'B' NM_SHIP, '임꼬물' NM_CREW
) A
WHERE A.NM_SHIP = 'A'
FOR XML PATH('')

추가 요구 조건 2 : 맨 앞에 있는 쉼표를 제거하고 싶다.
SELECT STUFF((SELECT ','+A.NM_CREW
FROM (
SELECT 'A' NM_SHIP, '김바둑' NM_CREW UNION ALL
SELECT 'A' NM_SHIP, '강누렁' NM_CREW UNION ALL
SELECT 'A' NM_SHIP, '박흰둥' NM_CREW UNION ALL
SELECT 'A' NM_SHIP, '장까미' NM_CREW UNION ALL
SELECT 'A' NM_SHIP, '곽얼룩' NM_CREW UNION ALL
SELECT 'B' NM_SHIP, '이나비' NM_CREW UNION ALL
SELECT 'B' NM_SHIP, '고치즈' NM_CREW UNION ALL
SELECT 'B' NM_SHIP, '황삼색' NM_CREW UNION ALL
SELECT 'B' NM_SHIP, '신고등' NM_CREW UNION ALL
SELECT 'B' NM_SHIP, '임꼬물' NM_CREW
) A
WHERE A.NM_SHIP = 'A'
FOR XML PATH('')),1,1,'')
