[오라클] MIN DECODE로 행열 변환

WITH TYPE_CNT AS (

    SELECT    'ALL'    AS TYPE

            , COUNT(1) AS CNT

    FROM     WEB_LOGIN

    UNION ALL

    SELECT   CASE RESTING_TYPE WHEN '휴면' THEN 'RESTING' ELSE 'USE' END AS TYPE

           , COUNT(WL.RESTING_TYPE) AS CNT

    FROM    WEB_LOGIN      WL

    GROUP BY RESTING_TYPE

    UNION ALL

    

    SELECT    'PC_ALL'    AS TYPE

            , COUNT(1) AS CNT

    FROM     CX_DEVICE_RESP

    

    UNION ALL

    SELECT  CASE X_RESTING_TYPE WHEN '활성' THEN 'PC_USE' ELSE 'PC_RESTING' END AS TYPE

            , COUNT(X_RESTING_TYPE) AS CNT

    FROM    CX_DEVICE_RESP

    GROUP BY X_RESTING_TYPE

)

SELECT   MIN(DECODE(TYPE, 'ALL', CNT)) AS USER_ALL

        ,MIN(DECODE(TYPE, 'RESTING', CNT)) AS USER_RESTING

        ,MIN(DECODE(TYPE, 'USE', CNT)) AS USER_USE

        ,MIN(DECODE(TYPE, 'PC_ALL', CNT)) AS PC_ALL

        ,MIN(DECODE(TYPE, 'PC_USE', CNT)) AS PC_USE

        ,MIN(DECODE(TYPE, 'PC_RESTING', CNT)) AS PC_RESTING

FROM   TYPE_CNT