핵심은 두가지가 될것 같으네요. 

1. recursive하게 number를 만드는 것 

2. 각 테이블에 더미로 rownumber를 만드는 것

그 다음은 조인이 될것 같으네요. 

참고요 

with

nums as (select 1 as r union all select r + 1 from nums where r < 10),

t1 as (

Select '1' as 현장코드, '작업자1' as 작업자, '250000' as 인건비 union all

Select '1' as 현장코드, '작업자2' as 작업자, '150000' as 인건비 ),

t2 as (Select '1' as 현장코드, '상도' as 구분, '재료1' as 재료명 union all

Select '1' as 현장코드, '중도(내화)' as 구분, '재료2' as 재료명 union all

Select '1' as 현장코드, '하도' as 구분, '재료3' as 재료명 ),

t3 as ( Select '1' as 현장코드, '스카이' as 품목, '경남스카이' as 업체명 union all

Select '1' as 현장코드, '스카이' as 품목, '경북스카이' as 업체명 union all

Select '1' as 현장코드, '스카이' as 품목, '충청스카이' as 업체명 union all

Select '1' as 현장코드, '스카이' as 품목, '서울' as 업체명 ),

t4 as ( Select '1' as 현장코드, '편의점' as 지출처, '홍길동' as 사용자 )

select

*

from

nums a

left outer join (select

row_number() over (order by (select 1)) as r, *

from t1) a1

on a.r = a1.r

left outer join (select

row_number() over (order by (select 1)) as r, *

from t2) a2

on a.r = a2.r

left outer join (select

row_number() over (order by (select 1)) as r, *

from t3 ) a3

on a.r = a3.r

left outer join (select

row_number() over (order by (select 1)) as r, *

from t4 ) a4

on a.r = a4.r

where

isnull(a1.r, 0) + isnull(a2.r, 0) + isnull(a3.r, 0) + isnull(a4.r, 0) > 0

정말 놀랍습니다. 감사합니다ㅠㅠ cte 실무에서 정말 많이 사용되는군요. 감사합니다!

정말 놀랍습니다. 감사합니다ㅠㅠ cte 실무에서 정말 많이 사용되는군요. 감사합니다!