꿈꾸는 개발자, DBA 커뮤니티 구루비

세로 데이터값을 가로 정렬 문의

by Taems [Oracle 기초] 가로 정렬 [2017.12.18 14:02:50]

안녕하세요. 세로 데이터를 가로 정렬하는 쿼리를 확인 중인데요.

아래는 세로로 나타나는 값들입니다.

데이터 값======================

YEAR	DIV	CODE	MEM
2008	FC	1	13125
2009	FC	1	13125
2010	FC	1	13125
2011	FC	1	13125
2012	FC	1	13125
2013	FC	1	13125
2014	FC	1	13125
2016	FC	6	13125
2008	FC	5	16471
2009	FC	5	16471
2010	FC	5	16471
2014	FD	5	16471
2015	FD	5	16471
2016	FD	5	16471
2017	FD	5	16471
2008	FC	3	13584
2010	FS	3	13584
2011	FS	3	13584
2012	FS	3	13584
2013	FS	3	13584
2014	FS	3	13584
2015	FS	3	13584
2016	FS	3	13584

야래의 가로 정렬 쿼리를 실행 해 보면 아래 잘못된 결과 처럼 나오는데

어느부분이 잘못된건지 아무리 수정해봐도 정상적으로 나와야할 결과 처럼 나오질 않습니다....

쿼리===========================================================

SELECT
DIV, MEM, CODE, SUBSTR( MAX( SYS_CONNECT_BY_PATH(YEAR, ' ') ) , 2) AS YEAR,
NVL(LENGTH(SUBSTR( MAX( SYS_CONNECT_BY_PATH(YEAR, ' ') ) , 2)) - LENGTH(REPLACE(SUBSTR( MAX( SYS_CONNECT_BY_PATH(YEAR, ' ') ) , 2), ' ', ''))+1, 0) YEAR_CNT
FROM (
(
SELECT
YEAR, DIV, MEM, CODE,
ROW_NUMBER() OVER( PARTITION BY MEM, CODE, DIV ORDER BY YEAR) RNUM
FROM
AT_FAC_LECTURER_ATTENDANCE
GROUP BY YEAR, DIV, MEM, CODE
)
)
START WITH RNUM = 1
CONNECT BY PRIOR RNUM = RNUM - 1 AND PRIOR MEM = MEM
GROUP BY MEM, CODE, DIV
ORDER BY MEM, CODE, DIV;

잘못된 결과 =============================================================

DIV	MEM	CODE	YEAR	YEAR_CNT
FC	13125	1	2016 2009 2010 2011 2012 2013 2014	7
FC	13125	6	2016	1
FC	13584	3	2008	1
FS	13584	3	2010 2011 2012 2013 2014 2015 2016	7
FC	16471	5	2014 2015 2010	3
FD	16471	5	2014 2015 2016 2017	4

정상적으로 나와야할 결과 ================================================

DIV	MEM	CODE	YEAR	YEAR_CNT
FC	13125	1	2008 2009 2010 2011 2012 2013 2014	7
FC	13125	6	2016	1
FC	13584	3	2008	1
FS	13584	3	2010 2011 2012 2013 2014 2015 2016	7
FC	16471	5	2008 2009 2010	3
FD	16471	5	2014 2015 2016 2017	4

위에 있는 정상적으로 나와야할 결과 처럼 뽑으려면 어느부분이 틀린건지 조언 부탁드리겠습니다. 오라클 버전은 10g 입니다.

눈이 많이 쌓였는데 눈길 조심하세요~~~~

by 고수가되고싶어요 [2017.12.18 14:17:46]

WITH T AS  (
SELECT '2008' YEAR , 'FC' DIV , 1 CODE , 13125 MEM FROM DUAL 
UNION ALL SELECT '2009' YEAR , 'FC' DIV , 1 CODE , 13125 MEM FROM DUAL 
UNION ALL SELECT '2010' YEAR , 'FC' DIV , 1 CODE , 13125 MEM FROM DUAL
UNION ALL SELECT '2011' YEAR , 'FC' DIV , 1 CODE , 13125 MEM FROM DUAL
UNION ALL SELECT '2012' YEAR , 'FC' DIV , 1 CODE , 13125 MEM FROM DUAL
UNION ALL SELECT '2013' YEAR , 'FC' DIV , 1 CODE , 13125 MEM FROM DUAL
UNION ALL SELECT '2014' YEAR , 'FC' DIV , 1 CODE , 13125 MEM FROM DUAL
UNION ALL SELECT '2016' YEAR , 'FC' DIV , 6 CODE , 13125 MEM FROM DUAL
UNION ALL SELECT '2008' YEAR , 'FC' DIV , 5 CODE , 16471 MEM FROM DUAL
UNION ALL SELECT '2009' YEAR , 'FC' DIV , 5 CODE , 16471 MEM FROM DUAL
UNION ALL SELECT '2010' YEAR , 'FC' DIV , 5 CODE , 16471 MEM FROM DUAL
UNION ALL SELECT '2014' YEAR , 'FD' DIV , 5 CODE , 16471 MEM FROM DUAL
UNION ALL SELECT '2015' YEAR , 'FD' DIV , 5 CODE , 16471 MEM FROM DUAL
UNION ALL SELECT '2016' YEAR , 'FD' DIV , 5 CODE , 16471 MEM FROM DUAL
UNION ALL SELECT '2017' YEAR , 'FD' DIV , 5 CODE , 16471 MEM FROM DUAL
UNION ALL SELECT '2008' YEAR , 'FC' DIV , 3 CODE , 13584 MEM FROM DUAL
UNION ALL SELECT '2010' YEAR , 'FS' DIV , 3 CODE , 13584 MEM FROM DUAL
UNION ALL SELECT '2011' YEAR , 'FS' DIV , 3 CODE , 13584 MEM FROM DUAL
UNION ALL SELECT '2012' YEAR , 'FS' DIV , 3 CODE , 13584 MEM FROM DUAL
UNION ALL SELECT '2013' YEAR , 'FS' DIV , 3 CODE , 13584 MEM FROM DUAL
UNION ALL SELECT '2014' YEAR , 'FS' DIV , 3 CODE , 13584 MEM FROM DUAL
UNION ALL SELECT '2015' YEAR , 'FS' DIV , 3 CODE , 13584 MEM FROM DUAL
UNION ALL SELECT '2016' YEAR , 'FS' DIV , 3 CODE , 13584 MEM FROM DUAL
)

SELECT DIV
	  ,MEM
      ,CODE
      --,SUBSTR(XMLAgg(XMLELEMENT(x, ' ', YEAR) ORDER BY YEAR).Extract('//text()'), 2) "9i"
      ,LISTAGG(YEAR,' ') WITHIN GROUP ( ORDER BY YEAR)    "11g"
      ,COUNT(*) YEAR_CNT 
FROM T 
GROUP BY DIV,MEM,CODE

--결과

FC   13125   1   2008 2009 2010 2011 2012 2013 2014   7
FC   13125   6   2016   1
FC   13584   3   2008   1
FC   16471   5   2008 2009 2010   3
FD   16471   5   2014 2015 2016 2017   4
FS   13584   3   2010 2011 2012 2013 2014 2015 2016   7

by Taems [2017.12.18 15:22:30]

감사합니다 고수가되고싶어요님~ 잘 참고 하도록하겠습니다~^^

제가 좀 복잡하게 짜고 있나봐요 ㅎㅎㅎ

by 마농 [2017.12.19 10:11:54]

1. 오류 원인은
- PARTITION BY mem, code, div 했으므로
- CONNECT BY 조건절에도 3개 컬럼 조건이 모두 와야 합니다.
- 현재는 mem 하나만 있네요. code, div 조건을 추가해야 하구요.
2. 결론은
- 계층쿼리에서 sys_connect_by_path 를 이용하는 방법은 비효율입니다.
- 다른 방법을 이용하세요.
- http://gurubee.net/article/55512

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변