|
이것저것 읽어보다 여기에 질문사항이 있어서 글을 남깁니다 ....
WITH V AS
(
select '1' TM, 'G1' LOC, 'AA' NM from dual union all
select '1', 'G1', 'BB' from dual union all
select '1', 'G1', 'CC' from dual union all
select '1', 'G2', 'DD' from dual union all
select '1', 'G2', 'EE' from dual union all
select '1', 'G2', 'FF' from dual union all
select '1', 'G2', 'GG' from dual union all
select '1', 'G3', 'HH' from dual union all
select '1', 'G3', 'II' from dual union all
select '1', 'G4', 'JJ' from dual union all
select '2', 'G1', 'KK' from dual union all
select '2', 'G1', 'LL' from dual union all
select '2', 'G2', 'MM' from dual union all
select '2', 'G2', 'NN' from dual union all
select '2', 'G2', 'OO' from dual union all
select '2', 'G2', 'PP' from dual union all
select '2', 'G3', 'QQ' from dual union all
select '2', 'G4', 'RR' from dual union all
select '2', 'G4', 'SS' from dual
)
SELECT TM
, MAX(CASE WHEN LOC = 'G1' THEN NM
ELSE NULL
END) G1
, MAX(CASE WHEN LOC = 'G2' THEN NM
ELSE NULL
END) G2
, MAX(CASE WHEN LOC = 'G3' THEN NM
ELSE NULL
END) G3
, MAX(CASE WHEN LOC = 'G4' THEN NM
ELSE NULL
END) G4
, COUNT(NM) SUM
FROM (
SELECT TM
, LOC
, NM
, ROW_NUMBER() OVER(PARTITION BY TM, LOC ORDER BY NM) RNUM
FROM V
)
GROUP BY TM, RNUM
ORDER BY TM, RNUM
|
