깡통

세로로 나열된 데이터를 가로로 보여주는 것을 영어로 Pivioting 시킨다고 표현을 하더군요.

SUM(DECODE(...)) 를 보셨는지 모르겠네요.

강력한 기능이죠..이걸 이용하면 pivoting 이 가능합니다.

제가 테스트한 데이터와 쿼리를 올립니다.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

========
# 데이터
========
                           acct        month       amt        
                      ---------- ----------- -----------
    1           1          10
    1           2          10
    1           3          10
    1           4          10
    1           5          10
    1           6          10
    1           7          10
    1           8          10
    1           9          10
    1          10          10
    1          11          10
    1          12          10
    2           1          20
    2           2          20
    2           3          20
    2           4          20
    2           5          20
    2           6          20
    2           7          20
    2           8          20
    2           9          20
    2          10          20
    2          11          20

============
# 데이터 생성
============

CREATE TABLE test1( acct NUMBER(2), month NUMBER(2), amt NUMBER(2));
   
   
INSERT INTO test1 VALUES(1,1,10);
INSERT INTO test1 VALUES(1,2,10);
INSERT INTO test1 VALUES(1,3,10);
INSERT INTO test1 VALUES(1,4,10);
INSERT INTO test1 VALUES(1,5,10);
INSERT INTO test1 VALUES(1,6,10);
INSERT INTO test1 VALUES(1,7,10);
INSERT INTO test1 VALUES(1,8,10);
INSERT INTO test1 VALUES(1,9,10);
INSERT INTO test1 VALUES(1,10,10);
INSERT INTO test1 VALUES(1,11,10);
INSERT INTO test1 VALUES(1,12,10);
INSERT INTO test1 VALUES(2,1,20);
INSERT INTO test1 VALUES(2,2,20);
INSERT INTO test1 VALUES(2,3,20);
INSERT INTO test1 VALUES(2,4,20);
INSERT INTO test1 VALUES(2,5,20);
INSERT INTO test1 VALUES(2,6,20);
INSERT INTO test1 VALUES(2,7,20);
INSERT INTO test1 VALUES(2,8,20);
INSERT INTO test1 VALUES(2,9,20);
INSERT INTO test1 VALUES(2,10,20);
INSERT INTO test1 VALUES(2,11,20);
INSERT INTO test1 VALUES(2,12,20);

==================================
# 쿼리 1 : SUM(DECODE(...)) 이용
==================================
SELECT acct,
       SUM(DECODE(month,1,amt,0)) as MON1,
       SUM(DECODE(month,2,amt,0)) as MON1,
       SUM(DECODE(month,3,amt,0)) as MON1,
       SUM(DECODE(month,4,amt,0)) as MON1,
       SUM(DECODE(month,5,amt,0)) as MON1,
       SUM(DECODE(month,6,amt,0)) as MON1,
       SUM(DECODE(month,7,amt,0)) as MON1,
       SUM(DECODE(month,8,amt,0)) as MON1,
       SUM(DECODE(month,9,amt,0)) as MON1,
       SUM(DECODE(month,10,amt,0)) as MON1,
       SUM(DECODE(month,11,amt,0)) as MON1,
       SUM(DECODE(month,12,amt,0)) as MON1
FROM test1
GROUP BY acct;     

        
==================================
# 쿼리 2 : 다른 함수 이용
==================================
SELECT acct,
   SUM(amt * (1 - ABS(SIGN(month - 1)))) as Mon1,
   SUM(amt * (1 - ABS(SIGN(month - 2)))) as Mon2,
   SUM(amt * (1 - ABS(SIGN(month - 3)))) as Mon3,
   SUM(amt * (1 - ABS(SIGN(month - 4)))) as Mon4,
   SUM(amt * (1 - ABS(SIGN(month - 5)))) as Mon5,
   SUM(amt * (1 - ABS(SIGN(month - 6)))) as Mon6,
   SUM(amt * (1 - ABS(SIGN(month - 7)))) as Mon7,
   SUM(amt * (1 - ABS(SIGN(month - 8)))) as Mon8,
   SUM(amt * (1 - ABS(SIGN(month - 9)))) as Mon9,
   SUM(amt * (1 - ABS(SIGN(month - 10)))) as Mon10,
   SUM(amt * (1 - ABS(SIGN(month - 11)))) as Mon11,
   SUM(amt * (1 - ABS(SIGN(month - 12)))) as Mon12
FROM test1
GROUP BY acct;

두번째 쿼리 만든사람 대단하지 않습니까..
http://www.ncst.ernet.in/~sybase/faq/Q2.3.html 에 있길래 가져왔는데,
아주 감탄스럽습니다..

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

이상 제가 테스트한 내용이구요, 여러분들의 필요에 맞게 활용하시면 되겠네요.
SUM 뿐만 아니라 AVG, MAX도 DECODE함수와 함께 쓰일 수 있습니다.
정보가 더 필요하다 생각되시면 찾아보면 될거같군요.

sample1)
select codeclass 망구분, codedisp 장비종류, codedesc 장비설명,
       sum(decode(equipstatus, '01', cnt, 0)) 운용,
       sum(decode(equipstatus, '02', cnt, 0)) 철거예정,
       sum(decode(equipstatus, '05', cnt, 0)) 불용대상,
       sum(cnt) 시스템수
  from
(SELECT d.codedisp codeclass, c.codedisp , c.codedesc, m.equipstatus, count(*) cnt
  FROM oss_equipmast m, oss_codemast c,oss_codemast d
 WHERE equiptypecode not in ('BTS')
   AND c.category = 'EquipType'
   AND m.equiptypecode = c.code
   AND d.category = 'CodeClass'
   AND c.class = d.code
   AND c.class != 'TRANSMISSION'
 GROUP BY d.codedisp,c.codedisp, c.codedesc, m.equipstatus
)
GROUP BY codeclass, codedisp, codedesc
ORDER BY 1,2;

SELECT MODEL_SEQ_NO
     , MIN(DECODE(EXAM_TYPE, '00002', PLATFORM_FAIL, NULL))    PLATFORM_FAIL
  , MIN(DECODE(EXAM_TYPE, '00003', FUNC_FAIL, NULL))    FUNC_FAIL
  FROM (SELECT A.MODEL_SEQ_NO
       , A.EXAM_TYPE
             , A.GEREXM_LOOP_TIME || '차'|| '(' || SUM(DECODE(A.EXAM_TYPE, '00002', DECODE( C.TESTRESULT_CODE,'00002',DECODE(C.EXAMSTD_SN, 'EF', 0, 1),0 ), 0) ) || ')'  PLATFORM_FAIL     --플랫폼검수불합격
             , A.GEREXM_LOOP_TIME || '차'|| '(' || SUM(DECODE(A.EXAM_TYPE, '00003', DECODE( C.TESTRESULT_CODE,'00002',DECODE(C.EXAMSTD_SN, 'EF', 0, 1),0 ), 0) ) || ')'  FUNC_FAIL         --기능검수불합격
          FROM T_PE_EXAM_INFO A         -- 현재차수
             , (SELECT MODEL_SEQ_NO, EXAM_TYPE, MAX(GEREXM_LOOP_TIME) GEREXM_LOOP_TIME FROM T_PE_EXAM_INFO WHERE MODEL_SEQ_NO = :MODEL_SEQ_NO AND EXAM_MOTION_STATUS = '00004' GROUP BY MODEL_SEQ_NO, EXAM_TYPE ) B         -- 이전차수
             , T_PE_EXAMSTD_RESULT C
         WHERE 1 = 1
           AND A.MODEL_SEQ_NO = :model_seq_no
           AND A.MODEL_SEQ_NO = B.MODEL_SEQ_NO
           AND A.EXAM_TYPE = B.EXAM_TYPE
     AND A.GEREXM_LOOP_TIME = B.GEREXM_LOOP_TIME
           AND A.MODEL_SEQ_NO = C.MODEL_SEQ_NO
           AND A.EXAM_TYPE = C.EXAM_TYPE
           AND A.GEREXM_LOOP_TIME =C.GEREXM_LOOP_TIME
         GROUP BY A.MODEL_SEQ_NO, A.EXAM_TYPE, A.GEREXM_LOOP_TIME)
 GROUP BY MODEL_SEQ_NO;
 
 
 
SELECT VM.MODEL_SEQ_NO
     , VM.GEREXM_LOOP_TIME || '차'|| '(' || SUM(DECODE( TESTRESULT_CODE,'00002',DECODE(EXAMSTD_SN, 'EF', 0, 1),0 )) || ')'  PLATFORM_FAIL
  , V2.FUNC_FAIL
  FROM (SELECT MODEL_SEQ_NO, EXAM_TYPE, MAX(GEREXM_LOOP_TIME) GEREXM_LOOP_TIME
          FROM T_PE_EXAM_INFO
       WHERE MODEL_SEQ_NO = :MODEL_SEQ_NO
     AND EXAM_TYPE = '00002'
     AND EXAM_MOTION_STATUS = '00004'
    GROUP BY MODEL_SEQ_NO, EXAM_TYPE) VM
     , T_PE_EXAMSTD_RESULT V1
     , (SELECT A1.MODEL_SEQ_NO
             , A1.GEREXM_LOOP_TIME || '차'|| '(' || SUM(DECODE( A2.TESTRESULT_CODE,'00002',DECODE(A2.EXAMSTD_SN, 'EF', 0, 1),0 )) || ')'  FUNC_FAIL
          FROM (SELECT MODEL_SEQ_NO, EXAM_TYPE, MAX(GEREXM_LOOP_TIME) GEREXM_LOOP_TIME
                  FROM T_PE_EXAM_INFO
                 WHERE MODEL_SEQ_NO = :MODEL_SEQ_NO
                   AND EXAM_TYPE = '00003'
                   AND EXAM_MOTION_STATUS = '00004'
                 GROUP BY MODEL_SEQ_NO, EXAM_TYPE) A1
             , T_PE_EXAMSTD_RESULT A2
         WHERE 1 = 1
           AND A1.MODEL_SEQ_NO = A2.MODEL_SEQ_NO
           AND A1.EXAM_TYPE = A2.EXAM_TYPE
           AND A1.GEREXM_LOOP_TIME = A2.GEREXM_LOOP_TIME
         GROUP BY A1.MODEL_SEQ_NO, A1.GEREXM_LOOP_TIME) V2
 WHERE 1 = 1
   AND VM.MODEL_SEQ_NO = :MODEL_SEQ_NO
   AND VM.MODEL_SEQ_NO = V1.MODEL_SEQ_NO
   AND VM.EXAM_TYPE = V1.EXAM_TYPE
   AND VM.GEREXM_LOOP_TIME =V1.GEREXM_LOOP_TIME
   AND VM.MODEL_SEQ_NO = V2.MODEL_SEQ_NO
 GROUP BY VM.MODEL_SEQ_NO, VM.GEREXM_LOOP_TIME, V2.FUNC_FAIL
;