ORACLE 행열 바꾸기 Feat.세로->가로

select user_Status, count(1)  as cnt
from A11.A1AT_USER_MASTER
group by user_Status;

1. DECODE 사용

WITH TEST AS (
    SELECT user_Status, COUNT(1) AS cnt
    FROM A11.A1AT_USER_MASTER
    GROUP BY user_Status
)
SELECT 
    MAX(DECODE(user_Status, 'R', CNT)) AS R,
    MAX(DECODE(user_Status, 'H', CNT)) AS H,
    MAX(DECODE(user_Status, 'Y', CNT)) AS Y,
    MAX(DECODE(user_Status, 'X', CNT)) AS X,
    MAX(DECODE(user_Status, 'N', CNT)) AS N
FROM TEST;

2. CASE 사용

select
    count(case when user_status = 'R' then user_status end) as R_CNT,
    count(case when user_status = 'H' then user_status end) as H_CNT,
    count(case when user_status = 'Y' then user_status end) as Y_CNT,
    count(case when user_status = 'X' then user_status end) as X_CNT,
    count(case when user_status = 'N' then user_status end) as N_CNT
from A11.A1AT_USER_MASTER;

3. FIVOT 사용

SELECT R_CNT, H_CNT, Y_CNT, X_CNT, N_CNT
FROM (
    SELECT USER_STATUS
    FROM A11.A1AT_USER_MASTER
)
PIVOT (
    COUNT(1) FOR USER_STATUS IN (
       'R' AS R_CNT,
       'H' AS H_CNT,
       'Y' AS Y_CNT,
       'X' AS X_CNT,
       'N' AS N_CNT
    )
)