[MSSQL] 세로 행을 가로 열로 문자열 합치기

세로 행을 가로 열로 문자열 합치기

STUFF와 FOR XML PATH를 활용한 문자열 합치기

#MSSQL #MSSQL 세로를 가로로 #MSSQL 문자열합치기 #MSSQL STUFF XML PATH

제가 문자열 합치기가 필요한 이유는
서로 다른 두개의 테이블을 조인을 걸때 키값이 일치하지 않을때 특정열을 표시하고 싶어도 
특정 열을 표시하면 세로로 데이터가 중복되어 늘어지는 것 때문입니다.

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

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

FOR XML : 쿼리의 실행 결과를 XML형식으로 만들어줍니다.


1. RAW : 행 집합의 각 행마다 SELECT문으로 반환되는 단일 행(ROW)를 생성합니다.

2. AUTO : SELECT문의 결과 XML에서 중첩된 구조를 생성합니다.

3. EXPLICIT : XML모양을 자신의 의지대로 특성과 요소를 혼합할 수 있습니다.

4. PATH : EXPLICIT 모드보다 좀 더 간편하게 XML형태를 가공할 수 있습니다.


1. 문자열 합치기

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

 

적용예제

TABLE WORK_RESULT_D 의 칼럼 A, B, C를 그룹 지어 LINEID를 표시하기

 

SELECT A

, B

, C

, STUFF ((   SELECT ',' + LINEID 
               FROM WORK_RESULT_D 
              WHERE A= A.A 
                 AND B= A.B
                AND C= A.C
         GROUP BY LINEID 
                FOR XML PATH ('') ), 1,1, '') AS LINEID 
 FROM WORK_RESULT_D AS A 
 WHERE ISNULL (DELETE_CHECK, 'F') <> 'T' 
GROUP BY A, B, C, LINEID 

 

다른 테이블과 조인걸린 모습  변경전 쿼리

SELECT K.WORK_NO, PACK.SALES_ORDER_IDX, M.LINEID, PACK.PACK_DATE WORK_DATE 
      , SUM(PACK.PACK_QTY) AS PACK_QTY 
      , DATEPART(DAY,PACK.PACK_DATE )  AS THISMMDAY 
FROM DBO.WORK_DIVIDE_M G  
  INNER JOIN DBO.WORK_DIVIDE_D H ON G.WORK_NO =H.WORK_NO AND  ISNULL(H.DELETE_CHECK , 'F') <> 'T' 
  INNER JOIN WORK_RESULT_M K ON K.WORK_NO = H.WORK_NO     
                                          AND K.SALES_ORDER_IDX = H.SALES_ORDER_IDX     

                                          AND  ISNULL(K.DELETE_CHECK , 'F') <> 'T'               
  INNER JOIN ( SELECT DISTINCT RESULT_NO, WORK_NO,SALES_ORDER_IDX,  LINEID
                                   FROM  WORK_RESULT_D AS A 
                                  WHERE  ISNULL(DELETE_CHECK , 'F') <> 'T' 
                                  GROUP BY RESULT_NO, WORK_NO  ,SALES_ORDER_IDX,LINEID  ) M

                                            ON K.RESULT_NO = M.RESULT_NO 
                                            AND H.WORK_NO = M.WORK_NO     
                                            AND H.SALES_ORDER_IDX = M.SALES_ORDER_IDX

다른 테이블과 조인걸린 모습  변경 후 쿼리

SELECT K.WORK_NO, PACK.SALES_ORDER_IDX, M.LINEID, PACK.PACK_DATE WORK_DATE
      , SUM(PACK.PACK_QTY) AS PACK_QTY
      , DATEPART(DAY,PACK.PACK_DATE )  AS THISMMDAY
FROM DBO.WORK_DIVIDE_M G 
  INNER JOIN DBO.WORK_DIVIDE_D H ON G.WORK_NO =H.WORK_NO AND  ISNULL(H.DELETE_CHECK , 'F') <> 'T'
  INNER JOIN WORK_RESULT_M K ON K.WORK_NO = H.WORK_NO    
                                          AND K.SALES_ORDER_IDX = H.SALES_ORDER_IDX     

                                          AND  ISNULL(K.DELETE_CHECK , 'F') <> 'T'              
  INNER JOIN ( SELECT DISTINCT RESULT_NO, WORK_NO,SALES_ORDER_IDX, 
                                         STUFF(( SELECT ',' + LINEID
                                                   FROM  WORK_RESULT_D
                                                   WHERE  RESULT_NO = A.RESULT_NO
                                                   AND  WORK_NO   = A.WORK_NO
                                                   AND  SALES_ORDER_IDX = A.SALES_ORDER_IDX
                                                  GROUP BY LINEID
                             FOR XML PATH('')  ),1,1,'') AS LINEID
                                   FROM  WORK_RESULT_D AS A
                                  WHERE  ISNULL(DELETE_CHECK , 'F') <> 'T'
                                  GROUP BY RESULT_NO, WORK_NO  ,SALES_ORDER_IDX,LINEID  ) M

                                            ON K.RESULT_NO = M.RESULT_NO
                                            AND H.WORK_NO = M.WORK_NO    
                                            AND H.SALES_ORDER_IDX = M.SALES_ORDER_IDX