데이터베이스 질문과 답변

SQL 질문 드립니다. 0 7 1,162

by oratong [2013.04.24 16:21:56]

BASI_SUST_CD MNGT_YY FORMA_SUST_CD DAYTM_FORMA_RCNT NGHT_FORMA_RCNT FORMA_RCNT
3200100140000 2013 3200100140000 80 30 110
3200100100000 2013 3200100100000 342 0 342
3200100140000 2012 3200100140000 80 30 110
3200100100000 2012 3200100100000 180 0 180
3200100140000 2011 3200100390000 80 0 80
3200100140000 2011 3200100140000 0 30 30
3200100100000 2011 3200100100000 210 0 210
3200100140000 2010 3200101490000 0 35 35
3200100140000 2010 3200101260000 80 0 80
3200100100000 2010 3200100100000 210 0 210
3200100140000 2009 3200101490000 0 40 40
3200100140000 2009 3200101260000 80 0 80
3200100140000 2008 3200101490000 0 30 30
3200100140000 2008 3200101260000 100 0 100
3200100140000 2007 3200101490000 0 30 30
3200100140000 2007 3200101260000 100 0 100


와 같은 데이터가 있습니다.

BASI_SUST_CD MNGT_YY1 FORMA_SUST_CD1 DAYTM_FORMA_RCNT1 NGHT_FORMA_RCNT1 FORMA_RCNT1 MNGT_YY11 FORMA_SUST_CD11 DAYTM_FORMA_RCNT11 NGHT_FORMA_RCNT11 FORMA_RCNT11 MNGT_YY111 FORMA_SUST_CD111 DAYTM_FORMA_RCNT111 NGHT_FORMA_RCNT111 FORMA_RCNT111 MNGT_YY1111 FORMA_SUST_CD1111 DAYTM_FORMA_RCNT1111 NGHT_FORMA_RCNT1111 FORMA_RCNT1111
3200100140000 2013 3200100140000 80 30 110 2012 3200100140000 80 30 110 2011 3200100390000 80 0 80 2010 3200101490000 0 35 35
　 　 　 　 3200100140000 0 30 30 　 3200101260000 80 0 80
3200100100000 2013 3200100100000 342 0 342 2012 3200100100000 180 0 180 2011 3200100100000 210 0 210 2010 3200100100000 210 0 210
　 　 　 　 　 　 　 　 　


와 같이 표현 하고 싶습니다.

1. FORMA_SUST_CD가 가변적으로  늘어날수도 줄어 들수도 있음.
2. BASI_SUST_CD를 기준으로 MNGT_YY 를 MNGT_YY1~MNGT_YY1111.... 의 형식으로 표현
3. MNGT_YY NULL 수도 있음 


create table sin (BASI_SUST_CD varchar2(20),
  MNGT_YY    varchar2(4),
  FORMA_SUST_CD varchar2(20),
  DAYTM_FORMA_RCNT number,
  NGHT_FORMA_RCNT  number,
  FORMA_RCNT number
  );

insert into sin values ('3200100140000', '2013', '3200100140000',  '80',  '30' ,'110');
insert into sin values ('3200100100000', '2013', '3200100100000',  '342', '0'  ,'342');
insert into sin values ('3200100140000', '2012', '3200100140000',  '80',  '30' ,'110');
insert into sin values ('3200100100000', '2012', '3200100100000',  '180', '0'  ,'180');
insert into sin values ('3200100140000', '2011', '3200100390000',  '80',  '0'  ,'80');
insert into sin values ('3200100140000', '2011', '3200100140000',  '0',   '30' ,'30');
insert into sin values ('3200100100000', '2011', '3200100100000',  '210', '0'  ,'210');
insert into sin values ('3200100140000', '2010', '3200101490000',  '0',   '35' ,'35');
insert into sin values ('3200100140000', '2010', '3200101260000',  '80',  '0'  ,'80');
insert into sin values ('3200100100000', '2010', '3200100100000',  '210', '0'  ,'210');
insert into sin values ('3200100140000', '2009', '3200101490000',  '0',   '40' ,'40');
insert into sin values ('3200100140000', '2009', '3200101260000',  '80',  '0'  ,'80');
insert into sin values ('3200100140000', '2008', '3200101490000',  '0',   '30' ,'30');
insert into sin values ('3200100140000', '2008', '3200101260000',  '100', '0'  ,'100');
insert into sin values ('3200100140000', '2007', '3200101490000',  '0',   '30' ,'30');
insert into sin values ('3200100140000', '2007', '3200101260000',  '100', '0'  ,'100');

commit;


by 우리집아찌 [2013.04.24 16:37:54]

혹시 옆으로 MNGT_YY 별로 무제한으로 늘어나나요?
아니면 현재년도에서 5년간 이런 규칙은 없나요?

by oratong [2013.04.24 17:16:27]

현재년도에서 10년으로 잡고 있습니다.

by 디케이 [2013.04.24 17:16:39] 
 
--쿼리로는 컬럼갯수를 자동으로 구현할 수 없고요 한 컬럼에 구분자를 이용해서 묶어야 합니다.
with
t(BASI_SUST_CD, MNGT_YY, FORMA_SUST_CD, DAYTM_FORMA_RCNT, NGHT_FORMA_RCNT, FORMA_RCNT) AS (
SELECT '3200100140000', '2013', '3200100140000', '80', '30' ,'110' FROM dual UNION ALL
SELECT '3200100100000', '2013', '3200100100000', '342', '0' ,'342' FROM dual UNION ALL
SELECT '3200100140000', '2012', '3200100140000', '80', '30' ,'110' FROM dual UNION ALL
SELECT '3200100100000', '2012', '3200100100000', '180', '0' ,'180' FROM dual UNION ALL
SELECT '3200100140000', '2011', '3200100390000', '80', '0' ,'80' FROM dual UNION ALL
SELECT '3200100140000', '2011', '3200100140000', '0', '30' ,'30' FROM dual UNION ALL
SELECT '3200100100000', '2011', '3200100100000', '210', '0' ,'210' FROM dual UNION ALL
SELECT '3200100140000', '2010', '3200101490000', '0', '35' ,'35' FROM dual UNION ALL
SELECT '3200100140000', '2010', '3200101260000', '80', '0' ,'80' FROM dual UNION ALL
SELECT '3200100100000', '2010' , '3200100100000', '210', '0' ,'210' FROM dual UNION ALL
SELECT '3200100140000', '2009', '3200101490000', '0', '40' ,'40' FROM dual UNION ALL
SELECT '3200100140000', '2009', '3200101260000', '80', '0' ,'80' FROM dual UNION ALL
SELECT '3200100140000', '2008', '3200101490000', '0', '30' ,'30' FROM dual UNION ALL
SELECT '3200100140000', '2008', '3200101260000', '100', '0' ,'100' FROM dual UNION ALL
SELECT '3200100140000', '2007', '3200101490000', '0', '30' ,'30' FROM dual UNION ALL
SELECT '3200100140000', '2007', '3200101260000', '100', '0' ,'100' FROM dual)

SELECT DECODE(lv, 1, BASI_SUST_CD, ' ')
 , listagg(NVL2(FORMA_SUST_CD, lpad(NVL(mngt_yy,' '),4), '  ')
 ||'|'||LPAD(NVL(FORMA_SUST_CD,' '), 13)
 ||'|'||LPAD(NVL(DAYTM_FORMA_RCNT,' '), 3)
 ||'|'||LPAD(NVL(NGHT_FORMA_RCNT, ' '), 3)
 ||'|'||LPAD(NVL(FORMA_RCNT,' '), 3), '▦') WITHIN GROUP(ORDER BY mngt_yy DESC NULLS LAST)
 FROM (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= (SELECT MAX(COUNT(1)) FROM t GROUP BY BASI_SUST_CD, MNGT_YY)) tt1
 LEFT OUTER JOIN 
 (SELECT BASI_SUST_CD, t1.MNGT_YY, FORMA_SUST_CD, DAYTM_FORMA_RCNT, NGHT_FORMA_RCNT, FORMA_RCNT
 , row_number() OVER (PARTITION BY BASI_SUST_CD, t1.MNGT_YY ORDER BY FORMA_SUST_CD DESC) rn
 FROM (SELECT DISTINCT mngt_yy FROM t) t1 LEFT OUTER JOIN t t2 PARTITION BY (t2.BASI_SUST_CD) ON t1.mngt_yy = t2.mngt_yy 
 ) tt2
 PARTITION BY (tt2.BASI_SUST_CD, tt2.MNGT_YY)
 ON tt1.lv = tt2.rn
 
 GROUP BY BASI_SUST_CD, lv
 ORDER BY BASI_SUST_CD DESC
;
by oratong [2013.04.24 20:26:06]
답변감사합니다 디케이 님 ^^
단지 || 들로 붙이는것이 아닌 각 칼럼의 값들이라서... 출력이 좀 다르게 나옵니다..
우리집 아찌님이 답변주신 출력 형태를 원하는 것이라..^^
디케이님이 답변주신 내용이 너무 공부하고 싶게 잘 짜여져 있어서 ㅎ
감사드립니다.
by 우리집아찌 [2013.04.24 18:27:52]

또 허접한 답올라갑니다 ㅡㅡ 4년치입니다. 나머지는 추가하세요

with
t(BASI_SUST_CD, MNGT_YY, FORMA_SUST_CD, DAYTM_FORMA_RCNT, NGHT_FORMA_RCNT, FORMA_RCNT) AS (
SELECT '3200100140000', '2013', '3200100140000', '80', '30' ,'110' FROM dual UNION ALL
SELECT '3200100100000', '2013', '3200100100000', '342', '0' ,'342' FROM dual UNION ALL
SELECT '3200100140000', '2012', '3200100140000', '80', '30' ,'110' FROM dual UNION ALL
SELECT '3200100100000', '2012', '3200100100000', '180', '0' ,'180' FROM dual UNION ALL
SELECT '3200100140000', '2011', '3200100390000', '80', '0' ,'80' FROM dual UNION ALL
SELECT '3200100140000', '2011', '3200100140000', '0', '30' ,'30' FROM dual UNION ALL
SELECT '3200100100000', '2011', '3200100100000', '210', '0' ,'210' FROM dual UNION ALL
SELECT '3200100140000', '2010', '3200101490000', '0', '35' ,'35' FROM dual UNION ALL
SELECT '3200100140000', '2010', '3200101260000', '80', '0' ,'80' FROM dual UNION ALL
SELECT '3200100100000', '2010' , '3200100100000', '210', '0' ,'210' FROM dual UNION ALL
SELECT '3200100140000', '2009', '3200101490000', '0', '40' ,'40' FROM dual UNION ALL
SELECT '3200100140000', '2009', '3200101260000', '80', '0' ,'80' FROM dual UNION ALL
SELECT '3200100140000', '2008', '3200101490000', '0', '30' ,'30' FROM dual UNION ALL
SELECT '3200100140000', '2008', '3200101260000', '100', '0' ,'100' FROM dual UNION ALL
SELECT '3200100140000', '2007', '3200101490000', '0', '30' ,'30' FROM dual UNION ALL
SELECT '3200100140000', '2007', '3200101260000', '100', '0' ,'100' FROM dual)


SELECT
  BASI_SUST_CD , NUM
  , MAX(MNGT_YY), MAX(FORMA_SUST_CD) , MAX(DAYTM_FORMA_RCNT) , MAX(NGHT_FORMA_RCNT) , MAX(FORMA_RCNT)
  , MAX(MNGT_YY_1), MAX(FORMA_SUST_CD_1) , MAX(DAYTM_FORMA_RCNT_1) , MAX(NGHT_FORMA_RCNT_1) , MAX(FORMA_RCNT_1)
  , MAX(MNGT_YY_2), MAX(FORMA_SUST_CD_2) , MAX(DAYTM_FORMA_RCNT_2) , MAX(NGHT_FORMA_RCNT_2) , MAX(FORMA_RCNT_2)
  , MAX(MNGT_YY_3), MAX(FORMA_SUST_CD_3) , MAX(DAYTM_FORMA_RCNT_3) , MAX(NGHT_FORMA_RCNT_3) , MAX(FORMA_RCNT_3)

FROM
(SELECT T.BASI_SUST_CD
  ,ROW_NUMBER() OVER(PARTITION BY  BASI_SUST_CD , MNGT_YY ORDER BY NULL ) NUM
  ,DECODE(MNGT_YY,TO_CHAR(SYSDATE ,'YYYY') , MNGT_YY) MNGT_YY   
  ,DECODE(MNGT_YY,TO_CHAR(SYSDATE ,'YYYY') , FORMA_SUST_CD)  FORMA_SUST_CD
  ,DECODE(MNGT_YY,TO_CHAR(SYSDATE ,'YYYY') , DAYTM_FORMA_RCNT)  DAYTM_FORMA_RCNT
  ,DECODE(MNGT_YY,TO_CHAR(SYSDATE ,'YYYY') , NGHT_FORMA_RCNT ) NGHT_FORMA_RCNT
  ,DECODE(MNGT_YY,TO_CHAR(SYSDATE ,'YYYY') , FORMA_RCNT ) FORMA_RCNT
  ,DECODE(MNGT_YY,TO_CHAR(SYSDATE ,'YYYY')-1 , MNGT_YY) MNGT_YY_1   
  ,DECODE(MNGT_YY,TO_CHAR(SYSDATE ,'YYYY')-1 , FORMA_SUST_CD)  FORMA_SUST_CD_1
  ,DECODE(MNGT_YY,TO_CHAR(SYSDATE ,'YYYY')-1 , DAYTM_FORMA_RCNT)  DAYTM_FORMA_RCNT_1
  ,DECODE(MNGT_YY,TO_CHAR(SYSDATE ,'YYYY')-1 , NGHT_FORMA_RCNT ) NGHT_FORMA_RCNT_1
  ,DECODE(MNGT_YY,TO_CHAR(SYSDATE ,'YYYY')-1 , FORMA_RCNT ) FORMA_RCNT_1
  ,DECODE(MNGT_YY,TO_CHAR(SYSDATE ,'YYYY')-2 , MNGT_YY) MNGT_YY_2   
  ,DECODE(MNGT_YY,TO_CHAR(SYSDATE ,'YYYY')-2 , FORMA_SUST_CD)  FORMA_SUST_CD_2
  ,DECODE(MNGT_YY,TO_CHAR(SYSDATE ,'YYYY')-2 , DAYTM_FORMA_RCNT)  DAYTM_FORMA_RCNT_2
  ,DECODE(MNGT_YY,TO_CHAR(SYSDATE ,'YYYY')-2 , NGHT_FORMA_RCNT ) NGHT_FORMA_RCNT_2
  ,DECODE(MNGT_YY,TO_CHAR(SYSDATE ,'YYYY')-2 , FORMA_RCNT ) FORMA_RCNT_2
  ,DECODE(MNGT_YY,TO_CHAR(SYSDATE ,'YYYY')-3 , MNGT_YY) MNGT_YY_3   
  ,DECODE(MNGT_YY,TO_CHAR(SYSDATE ,'YYYY')-3 , FORMA_SUST_CD)  FORMA_SUST_CD_3
  ,DECODE(MNGT_YY,TO_CHAR(SYSDATE ,'YYYY')-3 , DAYTM_FORMA_RCNT)  DAYTM_FORMA_RCNT_3
  ,DECODE(MNGT_YY,TO_CHAR(SYSDATE ,'YYYY')-3 , NGHT_FORMA_RCNT ) NGHT_FORMA_RCNT_3
  ,DECODE(MNGT_YY,TO_CHAR(SYSDATE ,'YYYY')-3 , FORMA_RCNT ) FORMA_RCNT_3   
  FROM T
) B
GROUP BY BASI_SUST_CD , NUM

by oratong [2013.04.24 20:16:12]

항상 친절한 도움에 여러가지를 많이 배웁니다.

저도 어서 여러분들에게 많은 도움 드리고 싶네요 ^^

디케이 님 우리집아찌님  너무 감사드립니다. ㅎ

by 손님 [2013.04.25 01:07:11] 
SELECT
 BASI_SUST_CD
 ,MAX(DECODE(COL_NO, 1, MNGT_YY))      MNGT_YY_1
 ,MAX(DECODE(COL_NO, 1, FORMA_SUST_CD))   FORMA_SUST_CD_1
 ,MAX(DECODE(COL_NO, 1, DAYTM_FORMA_RCNT)) DAYTM_FORMA_RCNT_1
 ,MAX(DECODE(COL_NO, 1, NGHT_FORMA_RCNT))  NGHT_FORMA_RCNT_1
 ,MAX(DECODE(COL_NO, 1, FORMA_RCNT))    FORMA_RCNT_1
 ,MAX(DECODE(COL_NO, 2, MNGT_YY))      MNGT_YY_2
 ,MAX(DECODE(COL_NO, 2, FORMA_SUST_CD))   FORMA_SUST_CD_2
 ,MAX(DECODE(COL_NO, 2, DAYTM_FORMA_RCNT)) DAYTM_FORMA_RCNT_2
 ,MAX(DECODE(COL_NO, 2, NGHT_FORMA_RCNT))  NGHT_FORMA_RCNT_2
 ,MAX(DECODE(COL_NO, 2, FORMA_RCNT))    FORMA_RCNT_2
 ,MAX(DECODE(COL_NO, 3, MNGT_YY))      MNGT_YY_3
 ,MAX(DECODE(COL_NO, 3, FORMA_SUST_CD))   FORMA_SUST_CD_3
 ,MAX(DECODE(COL_NO, 3, DAYTM_FORMA_RCNT)) DAYTM_FORMA_RCNT_3
 ,MAX(DECODE(COL_NO, 3, NGHT_FORMA_RCNT))  NGHT_FORMA_RCNT_3
 ,MAX(DECODE(COL_NO, 3, FORMA_RCNT))    FORMA_RCNT_3
 ,MAX(DECODE(COL_NO, 4, MNGT_YY))      MNGT_YY_4
 ,MAX(DECODE(COL_NO, 4, FORMA_SUST_CD))   FORMA_SUST_CD_4
 ,MAX(DECODE(COL_NO, 4, DAYTM_FORMA_RCNT)) DAYTM_FORMA_RCNT_4
 ,MAX(DECODE(COL_NO, 4, NGHT_FORMA_RCNT))  NGHT_FORMA_RCNT_4
 ,MAX(DECODE(COL_NO, 4, FORMA_RCNT))    FORMA_RCNT_4
 ,MAX(DECODE(COL_NO, 5, MNGT_YY))      MNGT_YY_5
 ,MAX(DECODE(COL_NO, 5, FORMA_SUST_CD))   FORMA_SUST_CD_5
 ,MAX(DECODE(COL_NO, 5, DAYTM_FORMA_RCNT)) DAYTM_FORMA_RCNT_5
 ,MAX(DECODE(COL_NO, 5, NGHT_FORMA_RCNT))  GHT_FORMA_RCNT_5
 ,MAX(DECODE(COL_NO, 5, FORMA_RCNT))    FORMA_RCNT_5
 ,MAX(DECODE(COL_NO, 6, MNGT_YY))      MNGT_YY_6
 ,MAX(DECODE(COL_NO, 6, FORMA_SUST_CD))   FORMA_SUST_CD_6
 ,MAX(DECODE(COL_NO, 6, DAYTM_FORMA_RCNT)) DAYTM_FORMA_RCNT_6
 ,MAX(DECODE(COL_NO, 6, NGHT_FORMA_RCNT))  NGHT_FORMA_RCNT_6
 ,MAX(DECODE(COL_NO, 6, FORMA_RCNT))    FORMA_RCNT_6
 ,MAX(DECODE(COL_NO, 7, MNGT_YY))      MNGT_YY_7
 ,MAX(DECODE(COL_NO, 7, FORMA_SUST_CD))   FORMA_SUST_CD_7
 ,MAX(DECODE(COL_NO, 7, DAYTM_FORMA_RCNT)) DAYTM_FORMA_RCNT_7
 ,MAX(DECODE(COL_NO, 7, NGHT_FORMA_RCNT))  NGHT_FORMA_RCNT_7
 ,MAX(DECODE(COL_NO, 7, FORMA_RCNT))    FORMA_RCNT_7
 ,MAX(DECODE(COL_NO, 8, MNGT_YY))      MNGT_YY_8
 ,MAX(DECODE(COL_NO, 8, FORMA_SUST_CD))   FORMA_SUST_CD_8
 ,MAX(DECODE(COL_NO, 8, DAYTM_FORMA_RCNT)) DAYTM_FORMA_RCNT_8
 ,MAX(DECODE(COL_NO, 8, NGHT_FORMA_RCNT))  NGHT_FORMA_RCNT_8
 ,MAX(DECODE(COL_NO, 8, FORMA_RCNT))    FORMA_RCNT_8
 ,MAX(DECODE(COL_NO, 9, MNGT_YY))      MNGT_YY_9
 ,MAX(DECODE(COL_NO, 9, FORMA_SUST_CD))   FORMA_SUST_CD_9
 ,MAX(DECODE(COL_NO, 9, DAYTM_FORMA_RCNT)) DAYTM_FORMA_RCNT_9
 ,MAX(DECODE(COL_NO, 9, NGHT_FORMA_RCNT))  NGHT_FORMA_RCNT_9
 ,MAX(DECODE(COL_NO, 9, FORMA_RCNT))    FORMA_RCNT_9
 ,MAX(DECODE(COL_NO,10, MNGT_YY))      MNGT_YY_10
 ,MAX(DECODE(COL_NO,10, FORMA_SUST_CD))   FORMA_SUST_CD_10
 ,MAX(DECODE(COL_NO,10, DAYTM_FORMA_RCNT)) DAYTM_FORMA_RCNT_10
 ,MAX(DECODE(COL_NO,10, NGHT_FORMA_RCNT))  GHT_FORMA_RCNT_10
 ,MAX(DECODE(COL_NO,10, FORMA_RCNT))    FORMA_RCNT_10
FROM (
 SELECT
  T.*
  ,ROW_NUMBER() OVER(PARTITION BY BASI_SUST_CD ,MNGT_YY ORDER BY FORMA_SUST_CD ) ROW_SEQ 
  ,TO_NUMBER(TO_CHAR(SYSDATE ,'YYYY')) - TO_NUMBER(MNGT_YY) + 1 COL_NO
 FROM T
 WHERE MNGT_YY >= TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE ,'YYYY')) - 9)
  AND MNGT_YY <= TO_CHAR(SYSDATE ,'YYYY') 
 )
GROUP BY BASI_SUST_CD ,ROW_SEQ
ORDER BY BASI_SUST_CD ,ROW_SEQ
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입