데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

Database

DBMS

MySQL

PostgreSQL

Community

Admin

Oracle Q&A 41532 게시물 읽기

No. 41532

세로형태 가로(일자 가변)형태로 만들기 도움 요청합니다.
작성자	김명찬(twman)	작성일	2018-03-06 20:44	조회수	6,303

안녕하세요

여전히 조금만 복잡해도 쿼리를 못해 이렇게 도움을 요청합니다. ㅠㅠ;;

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 '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

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 을 예제를 보며 하는데 잘 되지 않아 도움을 요청합니다.

감사합니다.

이 글에 대한 댓글이 총 1건 있습니다.

SQL 만으로는 가변열을 표현 할 수 없습니다.
프로그램에서 동적 SQL 을 구성하셔야 합니다.

SELECT *
FROM t
PIVOT (SUM(cnt) FOR dd IN ( '20171211' "20171211"
                           , '20171212' "20171212"
                           , '20171220' "20171220"
                           , '20171226' "20171226"
                           , '20171227' "20171227"
                           , '20180116' "20180116"
                           ) )
ORDER BY cd
;

마농(manon94)님이 2018-03-07 09:05에 작성한 댓글입니다.

[Top]

No.	제목	작성자	작성일	조회
41535	빈 row 추가 방법 문의 [1]	서준용	2018-03-19	5773
41534	DB OPEN 시도시 ERROR 문제 질문드려요. [1]	왕왕초보	2018-03-15	5834
41533	[강남] oracle 과외 해주실분 찾습니다.	박진철	2018-03-13	5884
41532	세로형태 가로(일자 가변)형태로 만들기 도움 요청합니다. [1]	김명찬	2018-03-06	6303
41531	Blob컬럼 Update 및 특정문자 붙여넣기	양정표	2018-03-05	5816
41530	Sqlldr 데이터 로드시 마지막구분자 없는데이터 어쩌죠? [1]	최종욱	2018-03-05	5913
41529	경우의 수 ? 짜는 로직이 있을까요? [2]	XRP에물림	2018-02-28	6509

작업시간: 0.018초, 이곳 서비스는
PostgreSQL v16.4로 자료를 관리합니다