[mssql] 두 행을 하나로 합치는 쿼리 (STUFF, XML PATH)

 

[mssql] 두 행을 하나로 합치는 쿼리 (STUFF, XML PATH)

위의 두 행을 하나로 보여주어야 한다. 정비지원장비사업자, nsss 제작사 이렇게 한 행으로 보여져야 하는 경우…

 

기존 쿼리

-- 기존 쿼리
SELECT 
    BIZ_CD_NM
FROM 
    SPM_BIZ_CODE
WHERE 
    BIZ_CD_SEQ IN ('49545','49546')

Stuff (문자열, 위치, 길이, 치환 할 문자)

SELECT 
    STUFF( BIZ_CD_SEQ, 1, 1, ',') AS BIZ_CD_SEQ
FROM 
      SPM_BIZ_CODE 
 WHERE 
    BIZ_CD_SEQ IN ('49545','49546')

 

XML PATH : XML 로 만들어줌

SELECT 
    BIZ_CD_NM
FROM 
    SPM_BIZ_CODE
WHERE 
    BIZ_CD_SEQ IN ('49545','49546')
FOR XML PATH('')

 

Stuff, XML PATH

-- STUFF (문자열, 위치, 길이, 치환 할 문자)
-- XML PATH
SELECT 
DISTINCT STUFF((SELECT ',' + BIZ_CD_NM
                FROM SPM_BIZ_CODE
                 WHERE BIZ_CD_SEQ IN ('49545','49546')
                 FOR XML PATH('')
       ), 1, 1, '') AS enames
  FROM SPM_BIZ_CODE

 

적용한 쿼리

SELECT
    DISTINCT -- 작성중일때 수신기관 합침.
    DOC.CORR_NO
    , DOC.CORR_TITLE
    , DOC.CORR_STATUS_CD
    , DOC.SEND_USER_SEQ
    , DOC.SEND_INST_SEQ
    , INST.CORR_SEQ
    , INST.INST_TYPE_CD
    , INST.BIZ_CD_SEQ
    , INST.RECEP_STATUS_CD
    , INST.RECEP_USER_SEQ
    , DBO.GETUSERNM(INST.RECEP_USER_SEQ) AS RECEPTION_USER_NM
    , INST.RECEP_DT
    , INST_SEND.RECEP_USER_SEQ AS SEND_USER_SEQ -- 발송
    , DBO.GETUSERNM(INST_SEND.RECEP_USER_SEQ) AS SEND_USER_NM
    , INST_SEND.RECEP_DT AS SEND_DT
    , DBO.GETCOMPANYNM(INST_SEND.RECEP_USER_SEQ) AS SEND_INST_NM
    , INST_RECEIVE.RECEP_USER_SEQ AS RECEIVE_USER_SEQ -- 수신
    , DBO.GETUSERNM(INST_RECEIVE.RECEP_USER_SEQ) AS RECEIVE_USER_NM
    , INST_RECEIVE.RECEP_DT AS RECEIVE_DT
    , CASE 
        WHEN DOC.CORR_STATUS_CD = 'WRITING' -- 작성중
        THEN STUFF((SELECT ',' + BIZ_CD_NM
                    FROM SPM_BIZ_CODE
                     WHERE BIZ_CD_SEQ IN (SELECT BIZ_CD_SEQ FROM DOM_DOC_INSTITUTE WHERE CORR_SEQ = INST.CORR_SEQ AND INST_TYPE_CD = 'RECEIVE') 
                     FOR XML PATH('')), 1, 1, '')  
        ELSE (SELECT BIZ_CD_NM FROM SPM_BIZ_CODE WHERE BIZ_CD_SEQ = INST_RECEIVE.BIZ_CD_SEQ) END
        AS RECEIVE_INST_NM
    , INST_RECEPTION.RECEP_USER_SEQ AS RECEPTION_USER_SEQ -- 접수
    , DBO.GETUSERNM(INST_RECEPTION.RECEP_USER_SEQ) AS RECEPTION_USER_NM
    , INST_RECEPTION.RECEP_DT AS RECEPTION_DT
    , (SELECT BIZ_CD_NM FROM SPM_BIZ_CODE WHERE BIZ_CD_SEQ = INST_RECEPTION.BIZ_CD_SEQ) AS RECEPTION_INST_NM
FROM
    DOM_DOC_INSTITUTE INST
    INNER JOIN DOM_DOC DOC
        ON DOC.CORR_SEQ = INST.CORR_SEQ
        AND DOC.DEL_YN = 'N'
        AND DOC.PARENT_CORR_SEQ != DOC.CORR_SEQ
    LEFT JOIN DOM_DOC_INSTITUTE INST_SEND -- 발송
        ON INST_SEND.CORR_SEQ = INST.CORR_SEQ
        AND INST_SEND.INST_TYPE_CD = 'SEND'
        AND INST_SEND.DEL_YN = 'N'
    LEFT JOIN DOM_DOC_INSTITUTE INST_RECEIVE -- 수신
        ON INST_RECEIVE.CORR_SEQ = INST.CORR_SEQ
        AND INST_RECEIVE.INST_TYPE_CD = 'RECEIVE'
        AND INST_RECEIVE.DEL_YN = 'N'
    LEFT JOIN DOM_DOC_INSTITUTE INST_RECEPTION -- 접수
        ON INST_RECEPTION.CORR_SEQ = INST.CORR_SEQ
        AND INST_RECEPTION.INST_TYPE_CD = 'RECEPTION'
        AND INST_RECEPTION.DEL_YN = 'N'
WHERE
    INST.BIZ_CD_SEQ = '2'
    AND INST.INST_TYPE_CD = 'SEND'
    AND INST.DEL_YN = 'N'
    AND INST.RECEP_USER_SEQ = '1'