데이터베이스 질문과 답변
ORACLE 가로 날짜 출력 문의
0
2
2,728
by lovely랑 [2010.04.12 21:19:09]
안녕하세요
COL_A COL_B COL_C
A 2010-04-12 120
B 2010-04-19 130
C 2010-04-13 140
D 2010-04-15 150
E 2010-04-11 160
이런데이터를
TYPE DATA_1 ~~~ DATE_12 DATE_13 DATE_31
2010-04-01 ~~~ 2010-04-12 2010-04-13 ~~~~ 2010-04-30
A 0 120 0
B 0 0 130
C
D
E
이런식으로 가로가 날짜가 나오고
그 합계순위가 세로로 나오게 할려면 어떻게 해야할까요?
COL_A COL_B COL_C
A 2010-04-12 120
B 2010-04-19 130
C 2010-04-13 140
D 2010-04-15 150
E 2010-04-11 160
이런데이터를
TYPE DATA_1 ~~~ DATE_12 DATE_13 DATE_31
2010-04-01 ~~~ 2010-04-12 2010-04-13 ~~~~ 2010-04-30
A 0 120 0
B 0 0 130
C
D
E
이런식으로 가로가 날짜가 나오고
그 합계순위가 세로로 나오게 할려면 어떻게 해야할까요?
by 마농
[2010.04.13 08:57:04]
SELECT col_a
, DECODE(SUBSTR(col_b,9),'01',col_c) "01"
, DECODE(SUBSTR(col_b,9),'02',col_c) "02"
, ...
, DECODE(SUBSTR(col_b,9),'31',col_c) "31"
FROM t
WHERE col_b LIKE '2010-04-%'
;
, DECODE(SUBSTR(col_b,9),'01',col_c) "01"
, DECODE(SUBSTR(col_b,9),'02',col_c) "02"
, ...
, DECODE(SUBSTR(col_b,9),'31',col_c) "31"
FROM t
WHERE col_b LIKE '2010-04-%'
;
by 패대기
[2010.04.13 10:02:50]
WITH t AS
(
SELECT 'A' col_a, '2010-04-12' col_b, 120 col_c from dual union all
SELECT 'B' col_a, '2010-04-19' col_b, 130 col_c from dual union all
SELECT 'C' col_a, '2010-04-13' col_b, 140 col_c from dual union all
SELECT 'D' col_a, '2010-04-15' col_b, 150 col_c from dual union all
SELECT 'E' col_a, '2010-04-11' col_b, 160 col_c from dual )
SELECT 'TYPE' col
, max(decode(col_b, '2010-04-11', col_b,0)) date11
, max(decode(col_b, '2010-04-12', col_b,0)) date12
, max(decode(col_b, '2010-04-13', col_b,0)) date13
, max(decode(col_b, '2010-04-15', col_b,0)) date15
, max(decode(col_b, '2010-04-19', col_b,0)) date19
FROM t
UNION ALL
SELECT col_a
, to_char(decode(col_b, '2010-04-11', col_c,0)) date11
, to_char(decode(col_b, '2010-04-12', col_c,0)) date12
, to_char(decode(col_b, '2010-04-13', col_c,0)) date13
, to_char(decode(col_b, '2010-04-15', col_c,0)) date15
, to_char(decode(col_b, '2010-04-19', col_c,0)) date19
FROM t
(
SELECT 'A' col_a, '2010-04-12' col_b, 120 col_c from dual union all
SELECT 'B' col_a, '2010-04-19' col_b, 130 col_c from dual union all
SELECT 'C' col_a, '2010-04-13' col_b, 140 col_c from dual union all
SELECT 'D' col_a, '2010-04-15' col_b, 150 col_c from dual union all
SELECT 'E' col_a, '2010-04-11' col_b, 160 col_c from dual )
SELECT 'TYPE' col
, max(decode(col_b, '2010-04-11', col_b,0)) date11
, max(decode(col_b, '2010-04-12', col_b,0)) date12
, max(decode(col_b, '2010-04-13', col_b,0)) date13
, max(decode(col_b, '2010-04-15', col_b,0)) date15
, max(decode(col_b, '2010-04-19', col_b,0)) date19
FROM t
UNION ALL
SELECT col_a
, to_char(decode(col_b, '2010-04-11', col_c,0)) date11
, to_char(decode(col_b, '2010-04-12', col_c,0)) date12
, to_char(decode(col_b, '2010-04-13', col_c,0)) date13
, to_char(decode(col_b, '2010-04-15', col_c,0)) date15
, to_char(decode(col_b, '2010-04-19', col_c,0)) date19
FROM t
) 버튼을 클릭하여 작성 하시면 됩니다.