[SQL]MS SQL STUFF/SQL Map/SQL List출력

안녕하세요. 글쓰는 개발자 입니다.

오늘은 STUFF 함수를 설명하고자 합니다.

 

업무를 하면서, Category형태로 하위항목이 2depth, 3depth씩 있을 때

부모 Depth를 Key, 그에 해당하는 자식Depth 내용을 List형태로 만들어 Value로

삽입해야 하는 경우가 종종 생깁니다.

그 때 Java단 혹은 JavaScript단에서 고생하실 필요없이,

한번에 SQL로 해결하는 방법을 알려드리겠습니다.

 

1. Table 자료형태

 

아래그림과 같이 address Table을 하나 생성했습니다.

1Depth Sido에 '부산'이 반복되고

2Depth Sigungu에 '남구'가 반복되고

3Depth Bemd에 읍면동이 자식항목으로 뿌려지고 있습니다.

Sido	Sigungu	Bemd
부산	남구	대연동
부산	남구	용호동
부산	남구	문현동
부산	남구	우암동
부산	남구	감만동

 

2. Key - Value 형태로 Data 출력

 

Web에서 DB의 Data를 뿌려줄 때 위 테이블을 예로들면,

부산 남구의 하위 항목을 리스트로 출력해야할 경우가 있습니다.

이를 위해서는 부산 또는 남구를 Key값으로,

대연동, 용호동, 문현동, 우암동, 감만동을 Value로 하는 HashMap형태가 되어야 합니다.

 

예시 : [Sido = 부산, Bemd = {대연동, 용호동, 문현동, 우암동, 감만동}]

 

그럴 때 위 data를 평범한 select문으로 조회하게 되면 Sido와 Sigungu가 반복됩니다

 

select * from address where Sido='부산'

 

따라서, STUFF 함수를 활용해, Key - Value 형태로 뽑아내야 합니다.

 

3. STUFF문 사용

 

참고링크 : https://docs.microsoft.com/ko-kr/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver15

STUFF(Transact-SQL) - SQL Server

STUFF 함수는 다른 문자열에 문자열을 삽입합니다. 

이 함수는 지정된 시작 위치와 문자 수에 따라 문자열의 문자를 삭제하고

두 번째 문자열을 시작 위치에 삽입합니다.

 

 

예시)

STUFF('[문자열]','[시작위치]','[크기]','[치환문자]')

STUFF ( character_expression , start , length , replaceWith_expression )

 

1) STUFF 함수 단독 사용 예시 및 결과

 

- 예시

      SELECT DISTINCT

 

      STUFF((SELECT ',' + BEMD FROM ADDRESS FOR XML PATH('')),

      1,1,'') AS BEMD

 

      FROM ADDRESS AS ADDR;

 

- 결과

 

2) STUFF 함수 활용 KEY - VALUE형태 출력

 

- 예시

 

SELECT DISTINCT SIDO, SIGUNGU, 

 

STUFF((SELECT ',' + BEMD FROM ADDRESS WHERE SIDO = ADDR.SIDO FOR XML PATH('')),

 1,1,'') AS BEMD

 

 FROM ADDRESS AS ADDR;

 

 

- 결과

 

4. 최종 결과 확인

 

아래 그림처럼 종으로 뻗어나가던 자료를

횡으로 LIST형태로 변환할 수 있었습니다.

자료를 종에서 횡형태로 변환

 

 

STUFF문을 잘 활용하셔서, 

JAVA단 및 JAVASCRIPT단에서 불필요한 작업 최소화 하시길 바라겠습니다.

 

감사합니다.