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)