데이터베이스 질문과 답변
안녕하세요.
차트 Data로 활용하기 위한 쿼리를 작성해야하는데요,
원하는 결과는 아래와 같습니다.
| ITEM | DATE | VALUE |
|---|---|---|
| A | 2014-11-17 | 1 |
| A | 2014-11-19 | 2 |
| A | 2014-11-20 | 3 |
| A | 2014-11-21 | 4 |
| B | 2014-11-17 | 4 |
| B | 2014-11-18 | 3 |
| B | 2014-11-21 | 5 |
| C | 2014-11-18 | 2 |
| C | 2014-11-19 | 2 |
| C | 2014-11-20 | 2 |
| C | 2014-11-21 | 2 |
| D | 2014-11-19 | 1 |
| D | 2014-11-20 | 4 |
| D | 2014-11-21 | 3 |
| DATE | A | B | C | D |
|---|---|---|---|---|
| 2014-11-17 | 1 | 4 | ||
| 2014-11-18 | 3 | 2 | ||
| 2014-11-19 | 2 | 2 | 1 | |
| 2014-11-20 | 3 | 2 | 4 | |
| 2014-11-21 | 4 | 5 | 2 | 3 |
이에 대한 조언좀 부탁드립니다.
감사합니다.
by 부쉬맨
[2014.11.21 14:18:16]
with t as
(
select 'A' as "ITEM" , '2014-11-17' as "D",1 as VALUE from dual union all
select 'A', '2014-11-19', 2 from dual union all
select 'A', '2014-11-20', 3 from dual union all
select 'A', '2014-11-21', 4 from dual union all
select 'B', '2014-11-17', 4 from dual union all
select 'B', '2014-11-18', 3 from dual union all
select 'B', '2014-11-21', 5 from dual union all
select 'C', '2014-11-18', 2 from dual union all
select 'C', '2014-11-19', 2 from dual union all
select 'C', '2014-11-20', 2 from dual union all
select 'C', '2014-11-21', 2 from dual union all
select 'D', '2014-11-19', 1 from dual union all
select 'D', '2014-11-20', 4 from dual union all
select 'D', '2014-11-21', 3 from dual
) select D ,
sum(case when item = 'A' then value end ) A,
sum(case when item = 'B' then value end ) B,
sum(case when item = 'C' then value end ) C,
sum(case when item = 'D' then value end ) D
from t
group by d
order by 1
by 마농
[2014.11.21 14:34:27]
-- 11G PIVOT --
SELECT *
FROM t
PIVOT (MIN(v) FOR item IN ('A' a, 'B' b, 'C' c, 'D' d))
ORDER BY dt
;
-- 이하 버전에서의 피벗, Group By & Min(Decode --
SELECT dt
, MIN(DECODE(item, 'A', v)) a
, MIN(DECODE(item, 'B', v)) b
, MIN(DECODE(item, 'C', v)) c
, MIN(DECODE(item, 'D', v)) d
FROM t
GROUP BY dt
ORDER BY dt
;
) 버튼을 클릭하여 작성 하시면 됩니다.