over(partition by) 예제
## 샘플 데이터
Row# CD SHOP_CD PER
1 SG A 9.37
2 SP B 14.71
3 SP A 5.71
Row# CD SHOP_CD PER
1 SG P 0
2 SG B 0
3 SG A 0
4 SP P 0
5 SP B 7.14
6 SP A 3.03
## CD 컬럼 기준으로 SHOP_CD 컬럼 데이터 그룹핑
with kk as (
select '1' as Row#, 'SG' as CD, 'A' as SHOP_CD, '9.37' as PER from dual
union all
select '2','SP','B','14.71' from dual
union all
select '3','SP','A','5.71' from dual
)
select CD,min(decode(cnt,1,SHOP_CD)) || ' & ' || min(decode(cnt,2,SHOP_CD))
from (select kk.*,row_number() over(partition by CD order by CD) cnt from kk) group by CD;
with kk as (
select '1' as Row#, 'SG' as CD, 'P' as SHOP_CD, '0' as PER from dual
union all
select '2','SG','B','0' from dual
union all
select '3','SG','A','0' from dual
union all
select '4','SP','P','5.71' from dual
union all
select '5','SP','B','7.14' from dual
union all
select '6','SP','A','3.03' from dual
)
select CD,min(decode(cnt,1,SHOP_CD)), min(decode(cnt,2,SHOP_CD)), min(decode(cnt,3,SHOP_CD))
from (select kk.*,row_number() over(partition by CD order by CD) cnt from kk)
group by CD;