데이터베이스 질문과 답변
아 쿼리 질문한개 드립니다. ㅠㅠ
0
1
1,540
by 김영훈 [2010.08.24 16:57:06]
select 점수 , 평균 , 년월 from schoolpoint;
result
점수 평균 년월
100 80 2005
200 90 2006
300 100 2007
200 80 2008
나온 값은 이런식인데 저는
2005 2006 2007 2008
점수 100 200 300 200
평균 80 90 100 80
이런식으로 나오게 할수는 없는건가여???
ㅠㅠ
고수님들 조언좀 해주세여
by 마농
[2010.08.25 08:05:30]
WITH schoolpoint AS
(
SELECT 100 점수, 80 평균, '2005' 년월 FROM dual
UNION ALL SELECT 200, 90, '2006' FROM dual
UNION ALL SELECT 300, 100, '2007' FROM dual
UNION ALL SELECT 200, 80, '2008' FROM dual
)
SELECT DECODE(lv,1,'점수',2,'평균') 구분
, MIN(DECODE(년월,'2005',v)) "2005"
, MIN(DECODE(년월,'2006',v)) "2006"
, MIN(DECODE(년월,'2007',v)) "2007"
, MIN(DECODE(년월,'2008',v)) "2008"
FROM
(
SELECT lv, 년월
, DECODE(lv,1,점수,2,평균) v
FROM schoolpoint
, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 2)
)
GROUP BY lv
;
(
SELECT 100 점수, 80 평균, '2005' 년월 FROM dual
UNION ALL SELECT 200, 90, '2006' FROM dual
UNION ALL SELECT 300, 100, '2007' FROM dual
UNION ALL SELECT 200, 80, '2008' FROM dual
)
SELECT DECODE(lv,1,'점수',2,'평균') 구분
, MIN(DECODE(년월,'2005',v)) "2005"
, MIN(DECODE(년월,'2006',v)) "2006"
, MIN(DECODE(년월,'2007',v)) "2007"
, MIN(DECODE(년월,'2008',v)) "2008"
FROM
(
SELECT lv, 년월
, DECODE(lv,1,점수,2,평균) v
FROM schoolpoint
, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 2)
)
GROUP BY lv
;
) 버튼을 클릭하여 작성 하시면 됩니다.