넵..ㅠㅠ

case를 이용하시는 방법밖에는 없을듯 합니다.

rmode가 가변적일 경우는 당연 동적 쿼리를 이용하셔야 하구요....

 
/*
WITH
DT1 as
(  select '09:00' s_time , '11:00' e_time ,'2013-2371B001' code ,'201' rmode union all
   select '11:00' ,'12:00' ,'2013-2371B001' ,'501' union all
   select '12:00' ,'16:00' ,'2013-2371C004' ,'201' )
   select * into #DT1  from DT1
  
WITH   
DT2 as
(
   select '10:00' s_time , '10:30' e_time   union all
   select '10:30' s_time , '11:00' e_time   union all
   select '11:00' s_time , '11:30' e_time   union all
   select '11:30' s_time , '12:00' e_time   union all
   select '12:00' s_time , '12:30' e_time   union all
   select '12:30' s_time , '13:00' e_time    )   
   select * into #DT2 from DT2
*/

     
SELECT B.s_time ,B.e_time
    ,  case when A.rmode = '201' then A.code end [201]
    ,  case when A.rmode = '501' then A.code end [501]
  FROM #DT2 B JOIN #DT1 A ON A.s_time <= B.s_time AND A.E_time >= B.e_time
       
     
-- 동적쿼리 ( 간단해서 서비스입니다. )
     
declare @sql varchar(max)

set @sql = 'SELECT B.s_time ,B.e_time ' + CHAR(10) ;

select @sql = @sql + '     , case when A.rmode = ''' +rmode +''' then A.code end ['+rmode+']' + CHAR(10)
  from #DT1 group by rmode
 
set @sql = @sql + '  FROM #DT2 B JOIN #DT1 A ON A.s_time <= B.s_time AND A.E_time >= B.e_time '
  

print @sql
exec(@sql)
 

감사합니다..ㅠㅠ

문제가 있내요...ㅠㅠ

select '12:00' ,'16:00' ,'2013-2371A005' ,'202'

동일시간에 202을 입력을 해서 보니, 12:00 부터 로우가 더 생겨버리내요..ㅠㅠ