database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 41985 게시물 읽기
No. 41985
이번에도 쿼리 좀 도와 주세요.
작성자
서주희
작성일
2024-02-26 12:16
조회수
227

안녕하세요.


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

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에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
41988ora 1008에러가 나는데 도움좀 부탁드립니다 [1]
2024-04-19
244
41987통계자료 쿼리 작성 가능할까요? [1]
이은정
2024-04-04
186
41986테이블간 relational 관계를 SQL로 추출이 가능할까요? [2]
빵빵이
2024-03-07
229
41985이번에도 쿼리 좀 도와 주세요. [1]
서주희
2024-02-26
227
41983쿼리 좀 도와주셔요.. [2]
서주희
2024-02-07
316
41982행의 값을 순차 차감하고 싶어요~ [1]
쿼리공부
2024-02-05
256
41981function에서 autonomous_transaction 사용 문의 [2]
최준영
2024-01-31
254
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.018초, 이곳 서비스는
	PostgreSQL v16.2로 자료를 관리합니다