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;

http://blog.daum.net/_blog/BlogTypeView.do?blogid=0KEP0&articleno=7609595&categoryId=591133®dt=20100106103323