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

Database

Community

자유게시판

도움주신분들

Admin

운영게시판

최근게시물

Oracle Q&A 41985 게시물 읽기

News | Q&A | Columns | Tutorials | Devel | Files | Links

No. 41985

이번에도 쿼리 좀 도와 주세요.
작성자	서주희	작성일	2024-02-26 12:16	조회수	440

안녕하세요.

이번에도 쿼리 좀 도와 주세요.

PIVOT 사용하면 될거 같은데 잘안되요..ㅠ

여기서 항상 많은 도움 받고 있는데 쿼리가 제일 어려운거 같아요..ㅠ

초기값

SELECT 'T' FLAG, '100' AS CD, 'A' AS CD2, 400 AS VAL1, 200 VAL2 FROM DUAL UNION ALL

SELECT 'T' FLAG, '100' AS CD, 'B' AS CD2, 400 AS VAL1, 200 VAL2 FROM DUAL UNION ALL

SELECT 'T' FLAG, '100' AS CD, 'C' AS CD2, 400 AS VAL1, 200 VAL2 FROM DUAL UNION ALL

SELECT 'T' FLAG, '200' AS CD, 'A' AS CD2, 500 AS VAL1, 200 VAL2 FROM DUAL UNION ALL

SELECT 'T' FLAG, '200' AS CD, 'B' AS CD2, 500 AS VAL1, 200 VAL2 FROM DUAL UNION ALL

SELECT 'T' FLAG, '200' AS CD, 'C' AS CD2, 500 AS VAL1, 200 VAL2 FROM DUAL UNION ALL

SELECT 'T' FLAG, '300' AS CD, 'A' AS CD2, 660 AS VAL1, 300 VAL2 FROM DUAL UNION ALL

SELECT 'T' FLAG, '300' AS CD, 'B' AS CD2, 660 AS VAL1, 300 VAL2 FROM DUAL UNION ALL

SELECT 'T' FLAG, '300' AS CD, 'C' AS CD2, 660 AS VAL1, 300 VAL2 FROM DUAL UNION ALL

SELECT 'F' FLAG, '100' AS CD, 'A' AS CD2, 400 AS VAL1, 200 VAL2 FROM DUAL UNION ALL

SELECT 'F' FLAG, '100' AS CD, 'B' AS CD2, 400 AS VAL1, 200 VAL2 FROM DUAL UNION ALL

SELECT 'F' FLAG, '100' AS CD, 'C' AS CD2, 400 AS VAL1, 200 VAL2 FROM DUAL UNION ALL

SELECT 'F' FLAG, '200' AS CD, 'A' AS CD2, 500 AS VAL1, 200 VAL2 FROM DUAL UNION ALL

SELECT 'F' FLAG, '200' AS CD, 'B' AS CD2, 500 AS VAL1, 200 VAL2 FROM DUAL UNION ALL

SELECT 'F' FLAG, '200' AS CD, 'C' AS CD2, 500 AS VAL1, 200 VAL2 FROM DUAL UNION ALL

SELECT 'F' FLAG, '300' AS CD, 'A' AS CD2, 660 AS VAL1, 300 VAL2 FROM DUAL UNION ALL

SELECT 'F' FLAG, '300' AS CD, 'B' AS CD2, 660 AS VAL1, 300 VAL2 FROM DUAL UNION ALL

SELECT 'F' FLAG, '300' AS CD, 'C' AS CD2, 660 AS VAL1, 300 VAL2 FROM DUAL

결과값

CD	FLAG1	DIV	A	B	C	FLAG1	DIV	A	B	C
100	T	VAL1	400	400	400	F	VAL1	400	400	400
200	T	VAL1	500	500	500	F	VAL1	500	500	500
300	T	VAL1	660	660	660	F	VAL1	660	660	660
100	T	VAL2	200	200	300	F	VAL2	200	200	300
200	T	VAL2	200	200	300	F	VAL2	200	200	300
300	T	VAL2	300	300	300	F	VAL2	300	300	300

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

WITH t AS

(

SELECT 'T' flag, '100' cd, 'A' cd2, 400 val1, 200 val2 FROM dual

UNION ALL SELECT 'T', '100', 'B', 400, 200 FROM dual

UNION ALL SELECT 'T', '100', 'C', 400, 200 FROM dual

UNION ALL SELECT 'T', '200', 'A', 500, 200 FROM dual

UNION ALL SELECT 'T', '200', 'B', 500, 200 FROM dual

UNION ALL SELECT 'T', '200', 'C', 500, 200 FROM dual

UNION ALL SELECT 'T', '300', 'A', 660, 300 FROM dual

UNION ALL SELECT 'T', '300', 'B', 660, 300 FROM dual

UNION ALL SELECT 'T', '300', 'C', 660, 300 FROM dual

UNION ALL SELECT 'F', '100', 'A', 400, 200 FROM dual

UNION ALL SELECT 'F', '100', 'B', 400, 200 FROM dual

UNION ALL SELECT 'F', '100', 'C', 400, 200 FROM dual

UNION ALL SELECT 'F', '200', 'A', 500, 200 FROM dual

UNION ALL SELECT 'F', '200', 'B', 500, 200 FROM dual

UNION ALL SELECT 'F', '200', 'C', 500, 200 FROM dual

UNION ALL SELECT 'F', '300', 'A', 660, 300 FROM dual

UNION ALL SELECT 'F', '300', 'B', 660, 300 FROM dual

UNION ALL SELECT 'F', '300', 'C', 660, 300 FROM dual

)

SELECT *

  FROM t

 UNPIVOT (v FOR div IN (val1, val2))

 PIVOT (MIN(v) FOR cd2 IN ('A' a, 'B' b, 'C' c))

 PIVOT (MIN(a) a, MIN(b) b, MIN(c) c FOR flag IN ('T' t, 'F' f))

 ORDER BY div, cd

;



-- div  는 기준값이라 굳이 두번 표시할 필요가 없고 -> 한번만 표시

-- flag 는 구분값이라 굳이 두번 표시할 필요가 없고 -> 컬럼명에 표시

마농(manon94)님이 2024-03-04 11:30에 작성한 댓글입니다.

No.	제목	작성자	작성일	조회
41988	ora 1008에러가 나는데 도움좀 부탁드립니다 [1]	덕	2024-04-19	491
41987	통계자료 쿼리 작성 가능할까요? [1]	이은정	2024-04-04	453
41986	테이블간 relational 관계를 SQL로 추출이 가능할까요? [2]	빵빵이	2024-03-07	481
41985	이번에도 쿼리 좀 도와 주세요. [1]	서주희	2024-02-26	440
41983	쿼리 좀 도와주셔요.. [2]	서주희	2024-02-07	527
41982	행의 값을 순차 차감하고 싶어요~ [1]	쿼리공부	2024-02-05	453
41981	function에서 autonomous_transaction 사용 문의 [2]	최준영	2024-01-31	442

All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.

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