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 41014 게시물 읽기
No. 41014
GROUP BY 부분과 ROW의 내용을 컬럼으로 올리는 부분을 조언 부탁드립니다.
작성자
이상영
작성일
2015-11-25 13:58ⓒ
2015-11-25 14:03ⓜ
조회수
9,216

안녕하세요 고수님들(^^)(__) 오라클 쿼리 관련 질문드립니다.

 

SELECT * FROM A_TABLE;

 

CODE | NM | SID | ADD

01 | 김 | 001 | 서울

02 | 이 | 002 | 부산

03 | 박 | 003 | 인천

....

 

SELECT * FROM B_TABLE;

 

CODE | TEST | TNM | TDATE | TSCORE

01 | 국어 | 작문 | 1997 | 95

01 | 국어 | 논술 | 1997 | 80

01 | 영어 | TIC | 2000 | 35

01 | 영어 | ACC | 2004 | 44

....

 

A_TABLE : B_TABLE의 관계는 1 : N 이고요 해당 PK 는 CODE 인데요

 

데이터를

CODE | NM | SID | ADD | 국어 | 영어

01 | 김 | 001 | 서울 | 작문 1997 95, 논술 1997 80 | TIC 2000 35, ACC 2004 44

02 | 이 | 002 | 부산 | 논술 1995 20, 발음 1999 30 | TES 2003 85, KEB 2011 49

.....

 

이와 같이 뿌려 주고 싶은데요, 많이 어려움을 겪고 있어서 도움 주시면 감사하겠습니다.

 

GROUP BY 부분과 ROW의 내용을 컬럼으로 올리는 부분이 많이 어렵습니다.

 

(ㅠㅠ)

 

 

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

WITH a_table AS
(
SELECT '01' code, '김' nm, '001' sid, '서울' addr FROM dual
UNION ALL SELECT '02', '이', '002', '부산' FROM dual
UNION ALL SELECT '03', '박', '003', '인천' FROM dual
)
, b_table AS
(
SELECT '01' code, '국어' test, '작문' ynm, '1997' tdate, 95 tscore FROM dual
UNION ALL SELECT '01', '국어', '논술', '1997', 80 FROM dual
UNION ALL SELECT '01', '영어', 'TIC' , '2000', 35 FROM dual
UNION ALL SELECT '01', '영어', 'ACC' , '2004', 44 FROM dual
UNION ALL SELECT '02', '국어', '논술', '1995', 20 FROM dual
UNION ALL SELECT '02', '국어', '발음', '1999', 30 FROM dual
UNION ALL SELECT '02', '영어', 'TES ', '2003', 85 FROM dual
UNION ALL SELECT '02', '영어', 'KEB ', '2011', 49 FROM dual
)
SELECT a.code, a.nm, a.sid, a.addr
     , b.국어, b.영어
  FROM a_table a
     , (SELECT code, test
             , LISTAGG(ynm||' '||tdate||' '||tscore, ' | ')
               WITHIN GROUP(ORDER BY tdate) v
          FROM b_table
         GROUP BY code, test
       ) PIVOT (MIN(v) FOR test IN ('국어' 국어, '영어' 영어)) b
 WHERE a.code = b.code
 ORDER BY a.code
;

마농(manon94)님이 2015-11-25 15:09에 작성한 댓글입니다.

감사합니다.

그런데

LISTAGG(ynm||' '||tdate||' '||tscore, ' | ')

WITHIN GROUP(ORDER BY tdate) v

요 부분은 11g이상에서 지원하는 함수여서

현재 9i 버전이다 보니 사용할 수가 없네요 .. ㅠㅠ

 

도움 주셨는데 죄송합니다.....

 

이상영님이 2015-11-25 16:35에 작성한 댓글입니다. Edit

SELECT a.code, a.nm, a.sid, a.addr
     , b.국어, b.영어
  FROM a_table a
     , (SELECT code
             , MIN(DECODE(test, '국어', v)) 국어
             , MIN(DECODE(test, '영어', v)) 영어
          FROM (SELECT code, test
                     , SUBSTR(XMLAGG(
                       XMLELEMENT(x, ' | ', ynm||' '||tdate||' '||tscore)
                       ORDER BY tdate).EXTRACT('//text()'), 4) v
                  FROM b_table
                 GROUP BY code, test
               )
         GROUP BY code
        ) b
 WHERE a.code = b.code
 ORDER BY a.code
;

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

 

9i 에서도 계층형쿼리 사용할 수 있을 꺼에요
select code, max(nm)as nm, max(sid)as sid, max(addr) as addr, max(국어) as 국어, max(영어) as 영어
from (
select code,nm,sid,addr, DECODE(test,'국어',SUBSTR(tt,2)) AS 국어 , DECODE(test,'영어',SUBSTR(tt,2)) AS 영어
from (
      select max(lv) over (partition by code,test) as maxlv ,lv,tt ,code ,test,nm,sid ,addr
      from (
        select a.code,a.nm,a.sid ,a.addr,level as lv  ,sys_connect_by_path(ynm||' '||tdate||' '||tscore, ',') as tt, b.test
        from a_table a
            ,b_table b
        where a.code = b.code
        connect by a.code = prior a.code 
        and        b.test = prior b.test
        and        b.ynm < prior b.ynm
      )
    )
where maxlv = lv
)
group by code;

Bill(baezzang)님이 2015-12-01 22:09에 작성한 댓글입니다.
이 댓글은 2015-12-01 22:09에 마지막으로 수정되었습니다.
[Top]
No.
제목
작성자
작성일
조회
41020Paging 처리방법 [1]
김영주
2015-12-01
8201
41016데이터베이스 SQL 작성했는데 어플라이가 보이지 않아요..!!ㅠㅜ
루루루
2015-11-28
8496
41015append parallel 힌트 질문 드립니다. (사용법) [1]
검콤이
2015-11-27
8791
41014GROUP BY 부분과 ROW의 내용을 컬럼으로 올리는 부분을 조언 부탁드립니다. [4]
이상영
2015-11-25
9216
41013global index 를 조회하는 방법이 있을까요? [4]
지나가는
2015-11-25
8963
41011오라클에서 다른 owner의 트리거 소스 볼 수 있는 권한 부여 방법 [1]
김광표
2015-11-24
8489
41010구간 시작 끝 묶음 및 일련번호 부여 문의 드립니다. [2]
김미림
2015-11-23
8285
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.052초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다