안녕하세요
여전히 조금만 복잡해도 쿼리를 못해 이렇게 도움을 요청합니다. ㅠㅠ;;
WITH t AS
(
select '7ATIV' as cd , '아티' as nm, 'amp' as unit, '20171211' as dd, '1' as cnt from dual union all
select '7DIAZ' as cd , '디아' as nm, 'amp' as unit, '20171226' as dd, '2' as cnt from dual union all
select '7MIDA' as cd , '미부' as nm, 'amp' as unit, '20171226' as dd, '1' as cnt from dual union all
select '7DIAZ' as cd , '디아' as nm, 'amp' as unit, '20171227' as dd, '3' as cnt from dual union all
select '7DIAZ' as cd , '디아' as nm, 'amp' as unit, '20171227' as dd, '-1' as cnt from dual union all
select '7DIAZ' as cd , '디아' as nm, 'amp' as unit, '20171227' as dd, '2' as cnt from dual union all
select 'ERDO' as cd , '도엘' as nm, 'cap' as unit, '20171211' as dd, '1' as cnt from dual union all
select 'VARI' as cd , '바리' as nm, 'tab' as unit, '20171211' as dd, '1' as cnt from dual union all
select 'TACER' as cd , '알이' as nm, 'tab' as unit, '20171211' as dd, '1' as cnt from dual union all
select 'COUF-PK' as cd , '코푸' as nm, 'pack' as unit, '20171211' as dd, '1' as cnt from dual union all
select 'DOXC' as cd , '제독' as nm, 'cap' as unit, '20171212' as dd, '1' as cnt from dual union all
select 'N-MYP' as cd , '마이' as nm, 'cap' as unit, '20171212' as dd, '6' as cnt from dual union all
select 'KEP50' as cd , '케프' as nm, 'tab' as unit, '20171212' as dd, '2' as cnt from dual union all
select 'KEP50' as cd , '케프' as nm, 'tab' as unit, '20171212' as dd, '-2' as cnt from dual union all
select 'TACER' as cd , '알이' as nm, 'tab' as unit, '20171212' as dd, '3' as cnt from dual union all
select 'ERDO' as cd , '도엘' as nm, 'cap' as unit, '20171212' as dd, '1' as cnt from dual union all
select 'TACER' as cd , '알이' as nm, 'tab' as unit, '20171220' as dd, '1' as cnt from dual union all
select 'TACER' as cd , '알이' as nm, 'tab' as unit, '20171220' as dd, '1' as cnt from dual union all
select 'TACER' as cd , '알이' as nm, 'tab' as unit, '20171220' as dd, '-1' as cnt from dual union all
select '7MIDA' as cd , '미부' as nm, 'amp' as unit, '20180116' as dd, '1' as cnt from dual
)
select * from t;
CD NM UNIT DD CNT
------- ------ ---- -------- ---
7ATIV 아티 amp 20171211 1
7DIAZ 디아 amp 20171226 2
7MIDA 미부 amp 20171226 1
7DIAZ 디아 amp 20171227 3
7DIAZ 디아 amp 20171227 -1
7DIAZ 디아 amp 20171227 2
ERDO 도엘 cap 20171211 1
VARI 바리 tab 20171211 1
TACER 알이 tab 20171211 1
COUF-PK 코푸 pack 20171211 1
DOXC 제독 cap 20171212 1
N-MYP 마이 cap 20171212 6
KEP50 케프 tab 20171212 2
KEP50 케프 tab 20171212 -2
TACER 알이 tab 20171212 3
ERDO 도엘 cap 20171212 1
TACER 알이 tab 20171220 1
TACER 알이 tab 20171220 1
TACER 알이 tab 20171220 -1
7MIDA 미부 amp 20180116 1
위와 같은 형태의 데이트를 아래 형태로 하려고 합니다.
cd |
nm |
unit |
20171211 |
20171212 |
20171220 |
20171226 |
20171227 |
20180116 |
7ATIV |
아티 |
amp |
1 |
|
|
|
|
|
7DIAZ |
디아 |
amp |
|
|
|
2 |
4 |
|
7MIDA |
미부 |
amp |
|
|
|
1 |
|
1 |
COUF-PK |
코푸 |
pack |
1 |
|
|
|
|
|
DOXC |
제독 |
cap |
|
1 |
|
|
|
|
ERDO |
도엘 |
cap |
1 |
1 |
|
|
|
|
KEP50 |
케프 |
tab |
|
0 |
|
|
|
|
N-MYP |
마이 |
cap |
|
6 |
|
|
|
|
TACER |
알이 |
tab |
1 |
3 |
1 |
|
|
|
VARI |
바리 |
tab |
1 |
|
|
|
|
|
unit 옆 날짜는 몇개가 될지 알수 없는 상황이라
pivot unpivot 을 예제를 보며 하는데 잘 되지 않아 도움을 요청합니다.
감사합니다.
|