|
안녕하세요...
아래 sql의 실행 결과는 다음과 같습니다.
select aa.CODE as codenm
, SUBSTR(bb.SBSCRB_DE,1,4) as yyyymm
,COUNT(bb.MBER_ID) as mbrcnt
FROM EGOV.COMTCCMMNDETAILCODE aa , TBO_CM_MBERINFO_LIST_BAK bb
WHERE aa.CODE_ID = 'MEM_GBN'
and bb.MBER_CONFM_CODE = 'Y'
AND aa.CODE = (DECODE(bb.MBER_SE_CODE,'7','1' ,'3','6', '4','6','9','1','10','1','11','6','12','1', bb.MBER_SE_CODE))
and bb.SBSCRB_DE is not null
group by aa.CODE , SUBSTR(bb.SBSCRB_DE,1,4)
order by 1, 2
구분 . . 년도 . . 카운트
1 . . 2005 . . 5
1 . . 2006 . . 20590
1 . . 2007 . . 4814
1 . . 2008 . . 1862
1 . . 2009 . . 1155
1 . . 2010 . . 981
1 . . 2011 . . 1229
1 . . 2012 . . 1659
1 . . 2013 . . 1147
1 . . 2014 . . 2699
1 . . 2015 . . 2858
1 . . 2016 . . 2376
13 . . 2007 . . 2
13 . . 2008 . . 1
13 . . 2009 . . 3
2 . . 2005 . . 649
2 . . 2006 . . 11099
2 . . 2007 . . 13433
2 . . 2008 . . 11814
2 . . 2009 . . 7500
2 . . 2010 . . 8159
2 . . 2011 . . 9519
2 . . 2012 . . 11460
2 . . 2013 . . 26719
2 . . 2014 . . 30081
2 . . 2015 . . 13004
2 . . 2016 . . 21049
5 . . 2006 . . 133
5 . . 2007 . . 50
5 . . 2008 . . 34
5 . . 2009 . . 217
5 . . 2010 . . 47
5 . . 2011 . . 32
5 . . 2012 . . 40
5 . . 2013 . . 91
5 . . 2014 . . 60
5 . . 2015 . . 30
5 . . 2016 . . 32
6 . . 2006 . . 2145
6 . . 2007 . . 8349
6 . . 2008 . . 5375
6 . . 2009 . . 5928
6 . . 2010 . . 5244
6 . . 2011 . . 7305
6 . . 2012 . . 7716
6 . . 2013 . . 10023
6 . . 2014 . . 10992
6 . . 2015 . . 8033
6 . . 2016 . . 6575
8 . . 2006 . . 93
8 . . 2007 . . 21
8 . . 2008 . . 51
8 . . 2009 . . 20
8 . . 2010 . . 23
8 . . 2011 . . 12
8 . . 2012 . . 22
8 . . 2013 . . 32
8 . . 2014 . . 34
8 . . 2015 . . 30
8 . . 2016 . . 71
위의 결과물을 아래와 같이 바꾸려면 어느 부분이 수정 되어야 하나요??
년도 구분1 구분2 구분3 구분4 구분5 구분6 구분7 구분8
2007 count1 count2 count3 count4 count5 count6 count7 count8
2008 count1 count2 count3 count4 count5 count6 count7 count8
2009 count1 count2 count3 count4 count5 count6 count7 count8
2010 count1 count2 count3 count4 count5 count6 count7 count8
2011 count1 count2 count3 count4 count5 count6 count7 count8
2012 count1 count2 count3 count4 count5 count6 count7 count8
2013 count1 count2 count3 count4 count5 count6 count7 count8
2014 count1 count2 count3 count4 count5 count6 count7 count8
2015 count1 count2 count3 count4 count5 count6 count7 count8
2016 count1 count2 count3 count4 count5 count6 count7 count8
2017 count1 count2 count3 count4 count5 count6 count7 count8
이 글에 대한 댓글이 총 2건 있습니다.
SELECT *
FROM (SELECT aa.code
, SUBSTR(bb.sbscrb_de, 1, 4) yyyy
, bb.mber_id
FROM egov.comtccmmndetailcode aa
, tbo_cm_mberinfo_list_bak bb
WHERE aa.code_id = 'MEM_GBN'
AND bb.mber_confm_code = 'Y'
AND aa.code
= CASE WHEN bb.mber_se_code IN ('7', '9', '10', '12') THEN '1'
WHEN bb.mber_se_code IN ('3', '4', '11' ) THEN '6'
ELSE bb.mber_se_code END
AND bb.sbscrb_de IS NOT NULL
)
PIVOT (COUNT(mber_id) FOR code IN ( '1' 구분1
, '2' 구분2
, '5' 구분5
, '6' 구분6
, '8' 구분8
, '13' 구분13
) )
ORDER BY yyyy
;
-----------------------------------------------------
안녕하세요..마농님.
마농님 께서 저번에 해결 해 주신 sql 입니다.
위 sql 결과에 한가지 추가 하려고 합니니다.
union all 도 써보고, sum 도 써 봤는데...잘 안 되네요..
한번 봐 주시기 부탁 드립니다.
원 하는 결과는
년도 구분1 구분2 구분3 구분4 구분5 구분6 구분7 구분8 합계
2007 count1 count2 count3 count4 count5 count6 count7 count8 count12345668
2008 count1 count2 count3 count4 count5 count6 count7 count8 count12345668
2009 count1 count2 count3 count4 count5 count6 count7 count8 count12345668
2010 count1 count2 count3 count4 count5 count6 count7 count8 count12345668
2011 count1 count2 count3 count4 count5 count6 count7 count8 count12345668
2012 count1 count2 count3 count4 count5 count6 count7 count8 count12345668
2013 count1 count2 count3 count4 count5 count6 count7 count8 count12345668
2014 count1 count2 count3 count4 count5 count6 count7 count8 count12345668
2015 count1 count2 count3 count4 count5 count6 count7 count8 count12345668
2016 count1 count2 count3 count4 count5 count6 count7 count8 count12345668
2017 count1 count2 count3 count4 count5 count6 count7 count8 count12345668
합계 c111111 c22222 c33333 c44444 c55555 c66666 c7777....................
이런 식으로 오른쪽 옆과, 아래 부분에 합계를 붙이고자 합니다.
|
