본문 바로가기
Oracle

[Sample] Pivot, multi pivot

by 캡틴노랑이 2015. 7. 31.
반응형
SELECT REQ_NO
     , CENT_CD
     , SPCM_COL_DM
     , BLD_NM
     , ZIP_CD
     , ADDR
     , ADDR_DTL
FROM
    (
      
    )
    PIVOT( MAX(ATTB_VAL) FOR ATTB_ID IN (
                             'CENT_CD'     AS HEL_CENT_CD
                           , 'SPCM_COL_DM'     AS SPCM_COL_DM
                           , 'BLD_NM' AS BLD_EQMT_NM
                           , 'ZIP_CD'      AS PAT_ZIP_CD 
                           , 'ADDR'        AS PAT_ADDR
                           , 'ADDR_DTL'    AS PAT_ADDR_DTL
                             )
    ) 
 

select *
from t1
pivot xml (
  sum(c3)
  for c2
  in (any)
)
;

select *
from t1
pivot xml (
  sum(c3)
  for c2
  in (select distinct c2 from t1)
)
;
 
select * 
from (
  select e.job
  ,   d.dname
  ,   e.empno
  from emp e
  ,   dept d
  where e.deptno = d.deptno
)
pivot (
  count(empno)
for job in ('CLERK','MANAGER','PRESIDENT','ANALYST','SALESMAN')

 multi pivot

WITH CTE AS (
  SELECT 
      A.ITEM_CD    
    , A.LOW_VAL
    , A.HIGH_VAL    
  FROM TB_REF A
  WHERE '20130801' between str_dt and end_dt
  AND SEX = 'M'
)
SELECT *
FROM CTE
PIVOT(
    MAX(LOW_VAL) AS L
  , MAX(HIGH_VAL) AS U
  FOR ITEM_CD IN (
                   'S0B7079900'    AS S0B7079900
                  ,'S0B7109900'    AS S0B7109900
                  ,'S0B7129900'    AS S0B7129900
                  ,'S0B7139900'    AS S0B7139900
                  ,'S0B7149900'    AS S0B7149900
                  ,'S0S4769900'    AS S0S4769900
                 )
)
;


반응형

'Oracle' 카테고리의 다른 글

[Query] Merge Into(다량의 건 일괄로)  (0) 2015.07.31
[Sample] Left outer join 비교  (0) 2015.07.31
[SQL Developer] 단축키  (0) 2015.07.31
[Query] Insert with  (0) 2015.07.31
SQL Developer 접속시  (0) 2015.07.29

댓글