데이터베이스 질문과 답변
오라클 행열 변환 문제
0
4
2,746
by 나나나나 [2015.12.16 18:49:25]
안녕하세요 초보 개발자입니다.
첨부와 같이 데이터를 뽑아야는데 방법을 잘 모르겠어서 문의드립니다.
by jkson
[2015.12.17 08:07:51]
규칙이 뭐죠?;
by 마농
[2015.12.17 09:18:25]
WITH t AS ( SELECT 1 dept, '가가호호1' nm, '팀원' pos FROM dual UNION ALL SELECT 1, '가가호호2' , '팀원' FROM dual UNION ALL SELECT 1, '가가호호3' , '팀원' FROM dual UNION ALL SELECT 1, '가가호호4' , '팀장' FROM dual UNION ALL SELECT 1, '가가호호5' , '팀장' FROM dual UNION ALL SELECT 1, '가가호호6' , '팀원' FROM dual UNION ALL SELECT 1, '가가호호7' , '팀장' FROM dual UNION ALL SELECT 3, '가가호호8' , '팀원' FROM dual UNION ALL SELECT 3, '가가호호9' , '팀원' FROM dual UNION ALL SELECT 3, '가가호호10', '팀원' FROM dual UNION ALL SELECT 3, '가가호호11', '팀장' FROM dual UNION ALL SELECT 3, '가가호호12', '팀장' FROM dual UNION ALL SELECT 3, '가가호호13', '팀원' FROM dual UNION ALL SELECT 5, '가가호호14', '팀원' FROM dual UNION ALL SELECT 5, '가가호호15', '팀원' FROM dual UNION ALL SELECT 5, '가가호호16', '팀원' FROM dual UNION ALL SELECT 5, '가가호호17', '팀장' FROM dual UNION ALL SELECT 5, '가가호호18', '팀장' FROM dual UNION ALL SELECT 5, '가가호호19', '팀장' FROM dual ) SELECT * FROM (SELECT dept, nm, pos , MIN(DECODE(pos||rn, '팀장1', nm)) OVER(PARTITION BY dept) mgr1 , MIN(DECODE(pos||rn, '팀장2', nm)) OVER(PARTITION BY dept) mgr2 , MIN(DECODE(pos||rn, '팀장3', nm)) OVER(PARTITION BY dept) mgr3 FROM (SELECT dept, nm, pos , ROW_NUMBER() OVER(PARTITION BY dept, pos ORDER BY nm) rn FROM t ) ) WHERE pos = '팀원' ;
by 창조의날개
[2015.12.17 09:30:32]
WITH TT(조직, 이름, 직책) AS(
SELECT 1,'가가호호1','팀원' FROM DUAL UNION ALL
SELECT 1,'가가호호2','팀원' FROM DUAL UNION ALL
SELECT 1,'가가호호3','팀원' FROM DUAL UNION ALL
SELECT 1,'가가호호4','팀장' FROM DUAL UNION ALL
SELECT 1,'가가호호5','팀장' FROM DUAL UNION ALL
SELECT 1,'가가호호6','팀원' FROM DUAL UNION ALL
SELECT 1,'가가호호7','팀장' FROM DUAL UNION ALL
SELECT 3,'가가호호8','팀원' FROM DUAL UNION ALL
SELECT 3,'가가호호9','팀원' FROM DUAL UNION ALL
SELECT 3,'가가호호10','팀원' FROM DUAL UNION ALL
SELECT 3,'가가호호11','팀장' FROM DUAL UNION ALL
SELECT 3,'가가호호12','팀장' FROM DUAL UNION ALL
SELECT 3,'가가호호13','팀원' FROM DUAL UNION ALL
SELECT 5,'가가호호14','팀원' FROM DUAL UNION ALL
SELECT 5,'가가호호15','팀원' FROM DUAL UNION ALL
SELECT 5,'가가호호16','팀원' FROM DUAL UNION ALL
SELECT 5,'가가호호17','팀장' FROM DUAL UNION ALL
SELECT 5,'가가호호18','팀장' FROM DUAL UNION ALL
SELECT 5,'가가호호19','팀장' FROM DUAL
)
SELECT XX.조직, 이름, 팀장1, 팀장2, 팀장3
FROM (
SELECT 조직
, MAX(DECODE(RN,1,이름)) 팀장1
, MAX(DECODE(RN,2,이름)) 팀장2
, MAX(DECODE(RN,3,이름)) 팀장3
FROM (
SELECT 조직, 이름, ROW_NUMBER() OVER(PARTITION BY 조직 ORDER BY 이름) RN
FROM TT AA
WHERE AA.직책 = '팀장'
)
GROUP BY 조직
) XX
,(
SELECT *
FROM TT AA
WHERE AA.직책 = '팀원'
) YY
WHERE XX.조직 = YY.조직
;
by 나나나나
[2015.12.17 16:46:40]
답변 감사합니다 ^^
) 버튼을 클릭하여 작성 하시면 됩니다.