MS-SQL 한 행으로 데이터 조회

문제점 😅

과일 이름을 조회해보자.

위와 같이 다중 ROW로 출력되는 데이터를 아래처럼 1 ROW로 콤마(,)로 구분짓는 문자열로 만들어 조회해야 하는 경우가 있다.

어떻게 해야 할까?

해결책 😏

2가지 방식이 있다.

1-1. FOR XML PATH(‘’) 구문 사용

FOR XML PATH('') 구문은 쉽게 말하면 XML 구문으로 데이터를 표기해주는 것인데, 자세히는 모르겠다. 이걸 사용하여 다중 ROW의 데이터를 1 ROW로 조회하자.

-- '(구분자)'를 붙여야 XML 형태가 아닌, (구분자)로 이어진 문자열 한 행이 출력됨.
SELECT ','+FRUIT_NAME
FROM FRUIT_BASKET
FOR XML PATH('')
;

-- 결과 => ,APPLE,BANANA,MELON

한 행으로 데이터를 만들었다. STUFF() 함수를 사용해서 APPLE 앞의 콤마를 지워야한다.

1-2. STUFF() 함수 사용

STUFF() 함수는 다른 문자열에 문자열을 삽입한다.

STUFF ( character_expression , start , length , replace_with_expression )

• character_expression: 문자 데이터의 식.
• start: 삭제 및 삽입을 시작할 위치.
• length: 삭제할 문자 수.
• replace_with_expression: 치환문자.

SELECT FRUIT_NAME
      ,STUFF(FRUIT_NAME, 3, 1, '*') AS REPLACE_STR
FROM FRUIT_BASKET
;

3번째 문자에서부터 시작하여 1개의 문자를 지우고 *로 치환했다. 결과는 다음과 같다.

따라서 원하는 결과를 조회해보자면, 다음과 같이 조회된다.

SELECT STUFF( /* '' 안의 구분자 문자열 길이가 */
              (SELECT ','+FRUIT_NAME 
               FROM FRUIT_BASKET
               FOR XML PATH('')
              )
             ,1
             ,1 /* 삭제할 문자 수와 동일해야 한다 */
             ,'') AS FRUIT_ARR
;

2. STRING_AGG() 함수 사용

STRING_AGG() 함수는 SQL Server 2017 이상 버전일 경우 사용 가능하다.

STRING_AGG ( expression, separator ) [ <order_clause> ]

<order_clause> ::=   
    WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )

• expression: 유효한 식으로, NVARCHAR 또는 VARCHAR 형식으로 변환.
• separator: 연결된 문자열의 구분 기호.
• order_clause: WITHIN GROUP 절을 사용하여 연결된 결과의 순서를 지정.

쉽게 말해서, STRING_AGG() 함수는 GROUP BY 절과 함께 사용해야 한다. ORDER BY 절을 사용하여 정렬이 가능하다.

SELECT AMOUNT
      ,STRING_AGG(FRUIT_NAME, ',') AS FRUIT_ARR
FROM FRUIT_BASKET
GROUP BY AMOUNT
;

AMOUNT로 GROUP BY 했다. 다음과 같다.

마무리 😎

어딜가나 다중 ROW를 1 ROW로 만들어서 조회해야 하는 경우는 있는 것 같다. 동일한 기능의 ORACLE 내장함수도 함께 기억해두자.

참고문헌 📝

https://gent.tistory.com/344
https://newdataflow.tistory.com/56