WITH dd(code, aname, bname, ROWNUMBER) as

(

select a.code, a.aname, b.bname, ROW_NUMBER() OVER(PARTITION BY a.code order BY a.code )

from @a as a

left outer join @b-2 as b on a.code=b.code

),

ddd(code, cname, ROWNUMBER) as

(

select c.code, c.cname, ROW_NUMBER() OVER(PARTITION BY code order BY code )

from @C as c

)

select COALESCE(d.code, d1.code) as code, COALESCE(aname,(select top 1 a.aname from @a a where a.code=code)) as aname, d.bname, d1.cname

from dd as d

full join ddd as d1 on d.code=d1.code and d.ROWNUMBER=d1.ROWNUMBER;

with d(name,code,ROW)

as(

SELECT bname,code,ROW_NUMBER() OVER(PARTITION BY code order by code) AS row

FROM @b-2 b

),

dd(name,code,ROW) as

(SELECT cname,code,ROW_NUMBER() OVER(PARTITION BY code order by code) AS row

FROM @C

)

SELECT a.* , b.name1, b.name2

FROM @a a

LEFT JOIN (

select d.code as code1, d.name as name1, dd.code as code2, dd.name as name2

from d

FULL JOIN dd ON d.code = dd.code AND d.row = dd.row ) b

ON a.code IN (b.code1, b.code2)