데이터베이스 질문과 답변
by 후레쉬 [2014.07.11 19:56:43]
세로로 나온 쿼리 결과물을 가로로 나타내려 합니다.
WITH T AS (
SELECT '서울' AS kind, '딸기' AS name, 3000 AS price, 30 AS amount FROM DUAL UNION ALL
SELECT '서울' AS kind, '사과' AS name, 2000 AS price, 20 AS amount FROM DUAL UNION ALL
SELECT '서울' AS kind, '참외' AS name, 2500 AS price, 25 AS amount FROM DUAL UNION ALL
SELECT '부산' AS kind, '딸기' AS name, 2300 AS price, 23 AS amount FROM DUAL UNION ALL
SELECT '부산' AS kind, '참외' AS name, 1800 AS price, 18 AS amount FROM DUAL UNION ALL
SELECT '대구' AS kind, '사과' AS name, 3200 AS price, 32 AS amount FROM DUAL
)
SELECT
name AS 종류,
MIN(DECODE(STD, 1, price)) 서울가격,
MIN(DECODE(STD, 1, amount)) 서울수량,
MIN(DECODE(STD, 2, price)) 부산가격,
MIN(DECODE(STD, 2, amount)) 부산수량,
MIN(DECODE(STD, 3, price)) 대구가격,
MIN(DECODE(STD, 3, amount)) 대구수량
FROM (
SELECT
ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) STD,
kind, NAME, price, amount
FROM T
)
GROUP BY name
order by name
제가 원하는 결과물은 아래와 같은데..
종류 서울(가격) 서울(수량) 부산(가격) 부산(수량) 대구(가격) 대구(수량)
딸기 3000 30 2300 23
사과 2000 20 3200 32
참외 2500 25 1800 18
실제 결과는 아래와 같이 나오네요..
종류 서울(가격) 서울(수량) 부산(가격) 부산(수량) 대구(가격) 대구(수량)
딸기 3000 30 2300 23
사과 2000 20 3200 32
참외 1800 18 2500 25
쿼리 어떻게 해야 할까요?
답변 부탁 드립니다 (__)
-- ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) STD, DENSE_RANK() OVER(ORDER BY kind DESC) STD,2.
SELECT name AS 종류,
MIN(DECODE(kind, '서울', price)) 서울가격,
MIN(DECODE(kind, '서울', amount)) 서울수량,
MIN(DECODE(kind, '부산', price)) 부산가격,
MIN(DECODE(kind, '부산', amount)) 부산수량,
MIN(DECODE(kind, '대구', price)) 대구가격,
MIN(DECODE(kind, '대구', amount)) 대구수량
FROM T
GROUP BY name
order by name
비주류님 답변 감사드립니다 (__)
좋은 하루 되세요 ^^
select name as "종류"
, s_v1 as "서울(가격)"
, s_v2 as "서울(수량)"
, b_v1 as "부산(가격)"
, b_v2 as "부산(수량)"
, d_v1 as "대구(가격)"
, d_v2 as "대구(수량)"
from t
pivot( min(price) as v1
, min(amount) as v2
for kind in ('서울' AS S
, '부산' AS B
, '대구' AS D)
)
order by name
;
오 pivot 이라는것을 이용해도 되는군요.
제로님 답변 감사 드립니다 ^^
pivot , unpivot 은 오라클 11g 이상에서 될겁니다. 참고하세요..
) 버튼을 클릭하여 작성 하시면 됩니다.