[MySQL] 여러 행 합쳐 출력하기(SQL Pivot) 개선

 

https://hojinjeong.tistory.com/111

 

 

 

지난번 올렸던 내용중 계좌들이 추가될 때마다 쿼리문이 수정되어야 하는 불편함이 있어 쿼리를 개선하였다.

---

3 데이터가 페어로 전환되어야함.

KFTC_CNTR_DEPOSIT_ACCOUNT_NUM
KFTC_CNTR_ACCOUNT_BANK_CODE
KFTC_CNTR_ACCOUNT_NUM

1, 2, 3, 4, +....N 개로 늘어날 수 있음.

예 :

KFTC_CNTR_ACCOUNT_NUM1
KFTC_CNTR_ACCOUNT_BANK_CODE1
KFTC_CNTR_DEPOSIT_ACCOUNT_NUM1

KFTC_CNTR_ACCOUNT_NUM2
KFTC_CNTR_ACCOUNT_BANK_CODE2
KFTC_CNTR_DEPOSIT_ACCOUNT_NUM2

KFTC_CNTR_ACCOUNT_NUM3
KFTC_CNTR_ACCOUNT_BANK_CODE3
KFTC_CNTR_DEPOSIT_ACCOUNT_NUM3

//... N 개 만큼 늘어날 수 있음.

---

SELECT sign, accountNum, depositAccountNum, bankCode, enable, updater, bank_name
FROM (SELECT sign,
             MAX(accountNum)        AS accountNum,
             MAX(depositAccountNum) AS depositAccountNum,
             MAX(bankCode)          AS bankCode,
             MIN(enable)            AS enable,
             MIN(updater)           AS updater
      FROM (
               (SELECT IF(SUBSTR(code, LENGTH('KFTC_CNTR_ACCOUNT_NUM') + 1) < 1, 0,
                          SUBSTR(code, LENGTH('KFTC_CNTR_ACCOUNT_NUM') + 1)) AS sign,
                       code_value                                            AS accountNum,
                       ''                                                    AS bankCode,
                       ''                                                    AS depositAccountNum,
                       enable,
                       updater,
                       update_time
                FROM albatross.v_kftc_config
                WHERE code LIKE 'KFTC_CNTR_ACCOUNT_NUM%')

               UNION ALL

               (SELECT IF(SUBSTR(code, LENGTH('KFTC_CNTR_DEPOSIT_ACCOUNT_NUM') + 1) < 1, 0,
                          SUBSTR(code, LENGTH('KFTC_CNTR_DEPOSIT_ACCOUNT_NUM') + 1)) AS sign,
                       ''                                                            AS accountNum,
                       ''                                                            AS bankCode,
                       code_value                                                    AS depositAccountNum,
                       enable,
                       updater,
                       update_time
                FROM albatross.v_kftc_config
                WHERE code LIKE 'KFTC_CNTR_DEPOSIT_ACCOUNT_NUM%')

               UNION ALL

               (SELECT IF(SUBSTR(code, LENGTH('KFTC_CNTR_ACCOUNT_BANK_CODE') + 1) < 1, 0,
                          SUBSTR(code, LENGTH('KFTC_CNTR_ACCOUNT_BANK_CODE') + 1)) AS sign,
                       ''                                                          AS accountNum,
                       code_value                                                  AS bankCode,
                       ''                                                          AS depositAccountNum,
                       enable,
                       updater,
                       update_time
                FROM albatross.v_kftc_config
                WHERE code LIKE 'KFTC_CNTR_ACCOUNT_BANK_CODE%')
           ) AS t
      GROUP BY sign) AS T
         INNER JOIN albatross.t_kftc_bank_code AS bankCode ON T.bankCode = bankCode.bank_code_std;

 

각각 컬럼들을 따로 구해 합치는 방식을 선택했다.