/********************************************************************************************
-- Title : [SQL2012] 조건별로 다른 ROW 가져오기
-- Key word : row partition by
********************************************************************************************/
-- 추출 조건
-- 같은 skey내에서 sect를 S > P순으로 가져오기.
use tempdb;
go
-- drop table ttt;
create table ttt
( skey int not null
, cola nvarchar(10) not null
, colb int not null
, sect nchar(1) not null
);
go
insert into ttt
select 111, 'aaa', 100, 'P' union all
select 111, 'bbb', 100, 'P' union all
select 111, 'ccc', 100, 'S' union all
select 111, 'ddd', 100, 'S' union all
select 222, 'eee', 100, 'S' union all
select 333, 'fff', 100, 'P' union all
select 333, 'ggg', 100, 'P' union all
select 333, 'hhh', 100, 'P' union all
select 444, 'iii', 100, 'S' union all
select 444, 'jjj', 100, 'S' union all
select 555, 'kkk', 100, 'P' union all
select 555, 'lll', 100, 'S' union all
select 666, 'mmm', 100, 'P';
go
select * from ttt;
go
/*************
-- CASE-1.
*************/
select *
from ttt as a
where sect = case when exists (select top 1 1 from ttt where skey = a.skey and sect = 'S')
then 'S'
else 'P'
end;
/*************
-- CASE-2.
*************/
select *
from
(
select *
, sum(case when sect ='P' then 1
when sect is null then 1
else 0 end ) over (partition by skey ) sect_p_t
, sum(case when sect ='S' then 1
else 0 end ) over (partition by skey ) sect_s_t
from ttt
) sub_2
where (sect_s_t > 0 and sect_p_t = 0) -- 'S'만 있는 데이터
or (sect_s_t = 0 and sect_p_t > 0) -- 'P'만 있는 데이터
or (sect_s_t > 0 and sect_p_t > 0 and sect = 's'); -- 둘다 있을때는 'S'만 가져오기