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 40738 게시물 읽기
No. 40738
[쿼리질문] 세로열을 가로열로 만들기
작성자
최혁준(joon912)
작성일
2015-03-17 10:01
조회수
7,983

안녕하세요

쿼리 질문이 있어서 문의 드립니다.

 

WITH T AS
(SELECT '김철수' AS EMPNO, '201411' AS YYMM, '100' AS KOREA, '90' AS ENGLISH FROM DUAL UNION ALL
 SELECT '김철수' AS EMPNO, '201412' AS YYMM, '100' AS KOREA, '90' AS ENGLISH FROM DUAL UNION ALL
 SELECT '김철수' AS EMPNO, '201501' AS YYMM, '100' AS KOREA, '90' AS ENGLISH FROM DUAL UNION ALL
 SELECT '김철수' AS EMPNO, '201502' AS YYMM, '100' AS KOREA, '90' AS ENGLISH FROM DUAL UNION ALL
 SELECT '이영호' AS EMPNO, '201412' AS YYMM, '100' AS KOREA, '90' AS ENGLISH FROM DUAL UNION ALL
 SELECT '이영호' AS EMPNO, '201501' AS YYMM, '100' AS KOREA, '90' AS ENGLISH FROM DUAL UNION ALL
 SELECT '이영호' AS EMPNO, '201502' AS YYMM, '100' AS KOREA, '90' AS ENGLISH FROM DUAL UNION ALL
 SELECT '박수영' AS EMPNO, '201411' AS YYMM, '100' AS KOREA, '90' AS ENGLISH FROM DUAL UNION ALL
 SELECT '박수영' AS EMPNO, '201412' AS YYMM, '100' AS KOREA, '90' AS ENGLISH FROM DUAL

)

SELECT * FROM T

위와 같은 쿼리를 쓰면

 

    EMPNO YYMM KOREA ENGLISH
1 김철수 201411 100 90
2 김철수 201412 100 90
3 김철수 201501 100 90
4 김철수 201502 100 90
5 이영호 201412 100 90
6 이영호 201501 100 90
7 이영호 201502 100 90
8 박수영 201411 100 90
9 박수영 201412 100 90

 

이렇게 나오는데요..

 

이런 쿼리를

 

EMPNO YYMM KOREA ENGLISH YYMM KOREA ENGLISH YYMM KOREA ENGLISH YYMM KOREA ENGLISH
김철수 201411 100 90 201412 100 90 201501 100 90 201502 100 90
이영호       201412 100 90 201501 100 90 201502 100 90
박수영 201411 100 90 201412 100 90            

이렇게 나오도록 할 수 있는게 가능 한건가요?

쿼리 한방으로 뽑고 싶은데 안된다며 프로그램을 짜야 될거 같아서 질문 드립니다.

 

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

WITH t AS
(
SELECT '김철수' empno, '201411' yymm, 100 korea, 90 english FROM dual
UNION ALL SELECT '김철수', '201412', 100, 90 FROM dual
UNION ALL SELECT '김철수', '201501', 100, 90 FROM dual
UNION ALL SELECT '김철수', '201502', 100, 90 FROM dual
UNION ALL SELECT '이영호', '201412', 100, 90 FROM dual
UNION ALL SELECT '이영호', '201501', 100, 90 FROM dual
UNION ALL SELECT '이영호', '201502', 100, 90 FROM dual
UNION ALL SELECT '박수영', '201411', 100, 90 FROM dual
UNION ALL SELECT '박수영', '201412', 100, 90 FROM dual
)
SELECT empno
     , MIN(DECODE(yymm, '201411', yymm   ))    yymm_201411
     , MIN(DECODE(yymm, '201411', korea  ))   korea_201411
     , MIN(DECODE(yymm, '201411', english)) english_201411
     , MIN(DECODE(yymm, '201412', yymm   ))    yymm_201412
     , MIN(DECODE(yymm, '201412', korea  ))   korea_201412
     , MIN(DECODE(yymm, '201412', english)) english_201412
     , MIN(DECODE(yymm, '201501', yymm   ))    yymm_201501
     , MIN(DECODE(yymm, '201501', korea  ))   korea_201501
     , MIN(DECODE(yymm, '201501', english)) english_201501
     , MIN(DECODE(yymm, '201502', yymm   ))    yymm_201502
     , MIN(DECODE(yymm, '201502', korea  ))   korea_201502
     , MIN(DECODE(yymm, '201502', english)) english_201502
  FROM t
 GROUP BY empno
;

마농(manon94)님이 2015-03-17 14:45에 작성한 댓글입니다.

마농님

답변 감사드립니다.

 

그런데 제가 설명에서 빼놓은 부분이 있었네요...

이 테이블에서 지정된 4개월만 옆으로 나열하는것이 아니고,

where절의 시작년월과 종료년월이 파라미터로 넘겨 받는 것이라

기간이 4달이 될수 있고, 6달, 12달도 될 수 있는 상황입니다.

그래서 정해진 열만큼 decode를 쓸 수 있는 것이 아니라서 임의의 년월에 해당하는 만큼

decode를 쓸 수 있는지를 질문 드린다는게 잘못올렸네요...

 

WITH T AS
(SELECT '김철수' AS EMPNO, '201411' AS YYMM, '100' AS KOREA, '90' AS ENGLISH FROM DUAL UNION ALL
 SELECT '김철수' AS EMPNO, '201412' AS YYMM, '100' AS KOREA, '90' AS ENGLISH FROM DUAL UNION ALL
 SELECT '김철수' AS EMPNO, '201501' AS YYMM, '100' AS KOREA, '90' AS ENGLISH FROM DUAL UNION ALL
 SELECT '김철수' AS EMPNO, '201502' AS YYMM, '100' AS KOREA, '90' AS ENGLISH FROM DUAL UNION ALL
 SELECT '이영호' AS EMPNO, '201412' AS YYMM, '100' AS KOREA, '90' AS ENGLISH FROM DUAL UNION ALL
 SELECT '이영호' AS EMPNO, '201501' AS YYMM, '100' AS KOREA, '90' AS ENGLISH FROM DUAL UNION ALL
 SELECT '이영호' AS EMPNO, '201502' AS YYMM, '100' AS KOREA, '90' AS ENGLISH FROM DUAL UNION ALL
 SELECT '박수영' AS EMPNO, '201411' AS YYMM, '100' AS KOREA, '90' AS ENGLISH FROM DUAL UNION ALL
 SELECT '박수영' AS EMPNO, '201412' AS YYMM, '100' AS KOREA, '90' AS ENGLISH FROM DUAL UNION ALL
 
 
 SELECT '박수영' AS EMPNO, '201504' AS YYMM, '100' AS KOREA, '90' AS ENGLISH FROM DUAL UNION ALL
 SELECT '박수영' AS EMPNO, '201506' AS YYMM, '100' AS KOREA, '90' AS ENGLISH FROM DUAL UNION ALL
 SELECT '박수영' AS EMPNO, '201507' AS YYMM, '100' AS KOREA, '90' AS ENGLISH FROM DUAL UNION ALL
 SELECT '박수영' AS EMPNO, '201508' AS YYMM, '100' AS KOREA, '90' AS ENGLISH FROM DUAL UNION ALL
 SELECT '박수영' AS EMPNO, '201509' AS YYMM, '100' AS KOREA, '90' AS ENGLISH FROM DUAL UNION ALL
 SELECT '김철수' AS EMPNO, '201504' AS YYMM, '100' AS KOREA, '90' AS ENGLISH FROM DUAL UNION ALL
 SELECT '김철수' AS EMPNO, '201506' AS YYMM, '100' AS KOREA, '90' AS ENGLISH FROM DUAL UNION ALL
 SELECT '김철수' AS EMPNO, '201507' AS YYMM, '100' AS KOREA, '90' AS ENGLISH FROM DUAL

)

SELECT * FROM T
WHERE YYMM >= '201411'
AND YYMM <= '201502'

최혁준(joon912)님이 2015-03-17 15:29에 작성한 댓글입니다.

쿼리만으로 해결할 수 있는 문제가 아닙니다.
프로그램에서 해당 월만큼 루프 돌려가면서
동적으로 위의 쿼리 문장을 완성하신 뒤
완성된 SQL을 실행하셔야 합니다.

마농(manon94)님이 2015-03-17 15:38에 작성한 댓글입니다.

네 쿼리로는 안되겠군요..

답변 감사드립니다.

최혁준(joon912)님이 2015-03-17 16:21에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
40746쿼리 퀴즈입니다.(파스칼 삼각형) [1]
김흥수
2015-03-18
6966
40745마농님 지정시간 관련 sql 문의드립니다. [2]
김태경
2015-03-17
7296
40744그룹해서 최대 데이터 4개 출력 [2]
윤민근
2015-03-17
6350
40738[쿼리질문] 세로열을 가로열로 만들기 [4]
최혁준
2015-03-17
7983
40737아우터 조인 관련 질문 [1]
궁금
2015-03-16
7257
40736조건 정시기준 분할 데이터 문의 드립니다. [3]
김태경
2015-03-16
7517
40735while문 이용해서 로그인 쿼리 [1]
권기범
2015-03-12
6636
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.017초, 이곳 서비스는
	PostgreSQL v16.2로 자료를 관리합니다