데이터베이스 질문과 답변
안녕하세요. 작업 진행중에 막히는 부분이 있어서 도움을 구하게 되었습니다. 스스로 해볼려고 여기저기 찾아서 보면서 해봤는데 잘 안되네요.. 시간이 너무 지체되어 이렇게 도움을 구하게 되었습니다. 죄송스럽지만 도움의 손길을 내밀어 주시면 감사드리겠습니다. 같은 id의 여러행을 열로 나열하여 하나의 행으로 만들려고 합니다. decode나 pivot으로 할려해봤는데 이해력이 딸려서 그런지 잘 안되더군요..
| ID | SEQ | TYPE | SAVE_TIME | POINT |
| 0000000202 | OP1 | C | 2022-05-12 04:30:00 PM | 3 |
| 0000000202 | OP2 | C | 2022-05-12 04:40:00 PM | 3 |
| 0000000202 | OP3 | C | 2022-05-12 04:50:00 PM | 4 |
| 0000000202 | OP4 | C | 2022-05-12 05:00:00 PM | 5 |
| 0000000202 | OP5 | C | 2022-05-12 05:10:00 PM | 5 |
| 0000000203 | OP1 | C | 2022-05-12 05:20:00 PM | 3 |
| 0000000203 | OP2 | C | 2022-05-12 05:30:00 PM | 3 |
| 0000000203 | OP3 | C | 2022-05-12 05:40:00 PM | 4 |
| 0000000203 | OP4 | C | 2022-05-12 05:50:00 PM | 5 |
| 0000000203 | OP5 | P | 5 |
이런 식의 데이터를
| ID | SUM_POINT | OP1 | OP2 | OP3 | OP4 | OP5 |
| 0000000202 | 20 | 2022-05-12 04:30:00 PM | 2022-05-12 04:40:00 PM | 2022-05-12 04:50:00 PM | 2022-05-12 05:00:00 PM | 2022-05-12 05:10:00 PM |
| 0000000203 | 15 | 2022-05-12 05:20:00 PM | 2022-05-12 05:30:00 PM | 2022-05-12 05:40:00 PM | 2022-05-12 05:50:00 PM |
이런 식으로 표현하고 싶습니다..
select '0000000202' as id, 'OP1' as seq, 'C' as type, to_date('2022-05-12 오후 4:30', 'yyyy-mm-dd am hh:mi') as save_time, '3' as point from dual union all
select '0000000202' as id, 'OP2' as seq, 'C' as type, to_date('2022-05-12 오후 4:40', 'yyyy-mm-dd am hh:mi') as save_time, '3' as point from dual union all
select '0000000202' as id, 'OP3' as seq, 'C' as type, to_date('2022-05-12 오후 4:50', 'yyyy-mm-dd am hh:mi') as save_time, '4' as point from dual union all
select '0000000202' as id, 'OP4' as seq, 'C' as type, to_date('2022-05-12 오후 5:00', 'yyyy-mm-dd am hh:mi') as save_time, '5' as point from dual union all
select '0000000202' as id, 'OP5' as seq, 'C' as type, to_date('2022-05-12 오후 5:10', 'yyyy-mm-dd am hh:mi') as save_time, '5' as point from dual union all
select '0000000203' as id, 'OP1' as seq, 'C' as type, to_date('2022-05-12 오후 5:20', 'yyyy-mm-dd am hh:mi') as save_time, '3' as point from dual union all
select '0000000203' as id, 'OP2' as seq, 'C' as type, to_date('2022-05-12 오후 5:30', 'yyyy-mm-dd am hh:mi') as save_time, '3' as point from dual union all
select '0000000203' as id, 'OP3' as seq, 'C' as type, to_date('2022-05-12 오후 5:40', 'yyyy-mm-dd am hh:mi') as save_time, '4' as point from dual union all
select '0000000203' as id, 'OP4' as seq, 'C' as type, to_date('2022-05-12 오후 5:50', 'yyyy-mm-dd am hh:mi') as save_time, '5' as point from dual union all
select '0000000203' as id, 'OP5' as seq, 'P' as type, null as save_time, '5' as point from dual;
* SUM_PIONT는 TYPE이 C인것의 합계입니다.
* OP가 현재는 5까지이지만 추후 늘어날것 같습니다.
by 우리집아찌
[2022.05.13 10:50:18]
seq는 5개 고정인가요?
by woni_rang
[2022.05.13 14:11:32]
고정이라고 하지만 추후 늘어날거 같습니다.
by 마농
[2022.05.13 11:06:00]
SELECT id
, SUM(point) sum_point
, MIN(DECODE(seq, 'OP1', save_time)) op1
, MIN(DECODE(seq, 'OP2', save_time)) op2
, MIN(DECODE(seq, 'OP3', save_time)) op3
, MIN(DECODE(seq, 'OP4', save_time)) op4
, MIN(DECODE(seq, 'OP5', save_time)) op5
FROM t
WHERE type = 'C'
GROUP BY id
;
by woni_rang
[2022.05.13 14:17:39]
와..감사합니다.
되게 간단하게 짜버리시네요..
저처럼 단순히 decode만 쓴다고 되는게 아니군요.. 또 배워 갑니다. (__)
) 버튼을 클릭하여 작성 하시면 됩니다.