기준월을 지정하면 그달부터 전년도 동월까지 1년치의 통계를 뽑는 쿼리를 작성하고있습니다.
아래 쿼리로 작성중인데요
SELECT
CASE
WHEN COL01 IN ('01001', '01003') THEN '2번'
WHEN COL01 IN ('02001') THEN '1번'
WHEN COL01 IN ('02002', '02003') THEN '3번'
ELSE '4번'
END
COL01,
TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-12), 'YYYYMM') AS mm01,
TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-11), 'YYYYMM') AS mm02,
TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-10), 'YYYYMM') AS mm03,
TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-9), 'YYYYMM') AS mm04,
TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-8), 'YYYYMM') AS mm05,
TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-7), 'YYYYMM') AS mm06,
TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-6), 'YYYYMM') AS mm07,
TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-5), 'YYYYMM') AS mm08,
TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-4), 'YYYYMM') AS mm09,
TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-3), 'YYYYMM') AS mm10,
TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-2), 'YYYYMM') AS mm11,
TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-1), 'YYYYMM') AS mm12,
TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), 0), 'YYYYMM') AS mm13,
DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-12), 'YYYYMM'), count(*)) AS val01,
DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-11), 'YYYYMM'), count(*)) AS val02,
DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-10), 'YYYYMM'), count(*)) AS val03,
DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-9), 'YYYYMM'), count(*)) AS val04,
DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), -8), 'YYYYMM'), count(*)) AS val05,
DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), -7), 'YYYYMM'), count(*)) AS val06,
DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), -6), 'YYYYMM'), count(*)) AS val07,
DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), -5), 'YYYYMM'), count(*)) AS val08,
DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), -4), 'YYYYMM'), count(*)) AS val09,
DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), -3), 'YYYYMM'), count(*)) AS val10,
DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), -2), 'YYYYMM'), count(*)) AS val11,
DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), -1), 'YYYYMM'), count(*)) AS val12,
DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), 0), 'YYYYMM'), count(*)) AS val13
FROM TESTTBL
WHERE SUBSTR(COL02,1,6) >= TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-12), 'YYYYMM')
AND SUBSTR(COL02,1,6) <= '201801'
GROUP BY
CASE
WHEN COL01 IN ('01001', '01003') THEN '2번'
WHEN COL01 IN ('02001') THEN '1번'
WHEN COL01 IN ('02002', '02003') THEN '3번'
ELSE '4번'
END, SUBSTR(COL02,1,6)
order by COL01
쿼리실행시 아래같이 나오는데 1달에 1row씩 4가지패턴으로 52row가 출력되는데요..
1번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
5854 |
|
|
|
|
|
|
|
|
|
|
|
|
1번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
5691 |
|
|
|
|
|
|
|
|
|
|
|
1번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
8140 |
|
|
|
|
|
|
|
|
|
|
1번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
9354 |
|
|
|
|
|
|
|
|
|
1번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
11365 |
|
|
|
|
|
|
|
|
1번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
15816 |
|
|
|
|
|
|
|
1번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
16038 |
|
|
|
|
|
|
1번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
|
16055 |
|
|
|
|
|
1번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
|
|
9435 |
|
|
|
|
1번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
|
|
|
4649 |
|
|
|
1번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
|
|
|
|
7293 |
|
|
1번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
|
|
|
|
|
8998 |
|
1번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
|
|
|
|
|
|
10104 |
2번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
1319 |
|
|
|
|
|
|
|
|
|
|
|
|
2번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
1308 |
|
|
|
|
|
|
|
|
|
|
|
2번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
1539 |
|
|
|
|
|
|
|
|
|
|
2번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
1739 |
|
|
|
|
|
|
|
|
|
2번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
2083 |
|
|
|
|
|
|
|
|
2번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
2337 |
|
|
|
|
|
|
|
2번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
2382 |
|
|
|
|
|
|
2번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
|
2260 |
|
|
|
|
|
2번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
|
|
1792 |
|
|
|
|
2번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
|
|
|
1189 |
|
|
|
2번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
|
|
|
|
1438 |
|
|
2번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
|
|
|
|
|
1329 |
|
2번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
|
|
|
|
|
|
1387 |
3번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
4501 |
|
|
|
|
|
|
|
|
|
|
|
|
3번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
5234 |
|
|
|
|
|
|
|
|
|
|
|
3번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
6994 |
|
|
|
|
|
|
|
|
|
|
3번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
7283 |
|
|
|
|
|
|
|
|
|
3번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
7716 |
|
|
|
|
|
|
|
|
3번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
8148 |
|
|
|
|
|
|
|
3번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
8009 |
|
|
|
|
|
|
3번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
|
8075 |
|
|
|
|
|
3번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
|
|
7162 |
|
|
|
|
3번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
|
|
|
4740 |
|
|
|
3번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
|
|
|
|
5169 |
|
|
3번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
|
|
|
|
|
5373 |
|
3번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
|
|
|
|
|
|
4496 |
4번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
5092 |
|
|
|
|
|
|
|
|
|
|
|
|
4번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
5564 |
|
|
|
|
|
|
|
|
|
|
|
4번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
5451 |
|
|
|
|
|
|
|
|
|
|
4번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
4542 |
|
|
|
|
|
|
|
|
|
4번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
5780 |
|
|
|
|
|
|
|
|
4번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
5395 |
|
|
|
|
|
|
|
4번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
4781 |
|
|
|
|
|
|
4번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
|
4495 |
|
|
|
|
|
4번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
|
|
3999 |
|
|
|
|
4번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
|
|
|
3224 |
|
|
|
4번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
|
|
|
|
5239 |
|
|
4번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
|
|
|
|
|
4978 |
|
4번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
|
|
|
|
|
|
|
|
|
|
|
|
4481 |
아래형식으로 공백을 제거하여 위쪽으로 몰아서 출력시키고 싶은데 당최 감이오질않습니다..
어떻게 해야 위로 몰수있을까요..
1번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
5854 |
5691 |
8140 |
9354 |
11365 |
15816 |
16038 |
16055 |
9435 |
4649 |
7293 |
8998 |
10104 |
2번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
1319 |
1308 |
1539 |
1739 |
2083 |
2337 |
2382 |
2260 |
1792 |
1189 |
1438 |
1329 |
1387 |
3번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
4501 |
5234 |
6994 |
7283 |
7716 |
8148 |
8009 |
8075 |
7162 |
4740 |
5169 |
5373 |
4496 |
4번 |
201701 |
201702 |
201703 |
201704 |
201705 |
201706 |
201707 |
201708 |
201709 |
201710 |
201711 |
201712 |
201801 |
5092 |
5564 |
5451 |
4542 |
5780 |
5395 |
4781 |
4495 |
3999 |
3224 |
5239 |
4978 |
4481 |
|