[Oracle] COLUMN을 ROW로 출력 (SUM+DECODE)

COLUMN을 ROW로 출력 (SUM+DECODE)

: ROW(행)를 COLUMN으로 출력

 

 

SELECT SUM(DECODE(column1, 100, column2)) as "100",
       SUM(DECODE(column1, 200, column2)) as "200",
       SUM(DECODE(column1, 300, column2)) as "300"
    FROM tablename;

: column1, column1별 합계 column2를 가로로 출력

 

 

SELECT column1, DECODE(column1, 100, column2) as "100"
    FROM tablename;

: column1이 100이면 column2를 출력, column1이 100이 아니면 NULL 출력

 

SELECT SUM(DECODE(column1, 100, column2)) as "100"
    FROM tablename;

: column1이 100이면 column2의 합계를 출력

 

 

 

SELECT SUM(DECODE(column1, 'KOREA', column2)) as "KOREA",
       SUM(DECODE(column1, 'USA', column2)) as "USA",
       SUM(DECODE(column1, 'GERMANY', column2)) as "GERMANY"
    FROM tablename;

: column1을 COLUMN명으로, column2의 합계를 COLUMN값(행)으로 출력

 

 

 

SELECT column1, SUM(DECODE(column2, 'KOREA', column3)) as "KOREA",
                SUM(DECODE(column2, 'USA', column3)) as "USA",
                SUM(DECODE(column2, 'GERMANY', column3)) as "GERMANY"
    FROM tablename
    GROUP BY column1;

: column1별 column2의 column3 합계의 분포 확인