[MSSQL] STUFF와 FOR XML 를 활용한 문자열 합치기(다건의 데이터 한줄 출력)

STUFF, FOR XML

STUFF : 지정된 문자열의 시작위치와 크기를 지정하여 원하는 문자로 치환하는 함수입니다.
STUFF('[문자열]','[시작위치]','[크기]','[치환문자]')

FOR XML : 쿼리의 실행 결과를 XML형식으로 만들어줍니다.
[모드]
1. RAW : 행 집합의 각 행마다 SELECT문으로 반환되는 단일 행(ROW)를 생성합니다.
2. AUTO : SELECT문의 결과 XML에서 중첩된 구조를 생성합니다.
3. EXPLICIT : XML모양을 자신의 의지대로 특성과 요소를 혼합할 수 있습니다.
4. PATH : EXPLICIT 모드보다 좀 더 간편하게 XML형태를 가공할 수 있습니다.

출처 : docs.microsoft.com/ko-kr/sql/relational-databases/xml/for-xml-sql-server?view=sql-server-ver15

FOR XML(SQL Server) - SQL Server

 

문자열 합치기 예제

TEST_TABLE안에 들어있는 데이터들. 이 데이터들을 합쳐서 출력해보겠습니다.

 

1. 문자열 합치기

SELECT DISTINCT STUFF(( SELECT ',' + Name FROM TEST_TABLE FOR XML PATH('') ),1,1,'') AS Name FROM TEST_TABLE AS TEST

SELECT DISTINCT STUFF(( SELECT ',' + Name FROM TEST_TABLE FOR XML PATH('') ),1,1,'') AS Name 
FROM TEST_TABLE AS TEST

 

2.  Seq번호로 그룹지어 출력하기

SELECT DISTINCT Seq, STUFF(( SELECT ',' + Name FROM TEST_TABLE WHERE Seq = TEST.Seq FOR XML PATH('') ),1,1,'') AS Name FROM TEST_TABLE AS TEST

SELECT DISTINCT 
	 Seq
    , STUFF(( 
    		SELECT ',' + Name 
            FROM TEST_TABLE 
            WHERE Seq = TEST.Seq FOR XML PATH('') ),1,1,'') AS Name 
FROM TEST_TABLE AS TEST

 

 

 

그리드 내  아이콘을 출력하기 위한 STUFF,FOR XML QUERY

WITH TEMP AS(
  SELECT 
    I.IDEAID
    ,C.CODENAME AS SITEID
    ,C1.CODENAME AS FIELD
    ,I.TYPE
    ,I.TITLE
    ,I.CREATOR
    ,I.NOTICETIME AS NOTICEDATE
    ,I.DOCNUM
    ,CASE WHEN (SELECT COUNT(*) FROM SF_FILEM WHERE FILEMID = I.FILEID) > 0 THEN 'Y' ELSE 'N' END AS FILEID	  
    ,U.USERNAME
    ,(SELECT COUNT(*) FROM BMP_IDEALIKE WHERE IDEAID = I.IDEAID AND VALIDSTATE = 'Valid') AS [LIKE]
    , (SELECT STUFF((SELECT DISTINCT ',' +SITEID FROM BMP_IDEASPREAD WHERE IDEAID=I.IDEAID FOR XML PATH ('')), 1,1,'') AS 'SPREADSITE') AS SPREADSITE
    , CASE WHEN APYN ='Y' THEN 'O'  ELSE ' '  END AS APYN
    ,I.MODIFIEDTIME
    ,I.CREATEDTIME
    ,NOTICETIME
  FROM BMP_IDEA I LEFT JOIN SF_CODE C
                          ON I.SITEID = C.CODEID
                          AND C.CODECLASSID='BMP_PLANT'	
                          LEFT JOIN SF_USER U
                          ON I.CREATOR = U.USERID
                          LEFT JOIN SF_CODE C1
                          ON I.FIELD = C1.CODEID
                          AND C1.CODECLASSID='IDEA_TYPE'

  WHERE 1=1 
  		AND I.VALIDSTATE = 'Valid'
      
  ) 
  SELECT * FROM TEMP WHERE 1=1
    	ORDER BY NOTICETIME DESC ,DOCNUM DESC
 

 

결과