All groups in a data region specify different views of the same report dataset.
SELECT * FROM ( VALUES (1, 'head 1'), (2, 'head 2') ) AS A(AKey, AName)left outer join ( VALUES (1, 'detail B head 1'), (2, 'detail B head 2') ) AS B(BKey, BName) on A.AKey = B.BKey left outer join ( VALUES (1, 'detail c head 1'), (1, 'detailc c head 2') ) AS C(CKey, CName) on A.AKey = C.CKey
WITH B AS ( SELECT *, row_number() OVER ( PARTITION BY BKey ORDER BY BKey ) AS rn FROM ( VALUES (1, 'detail B head 1'), (2, 'detail B head 2') ) AS BBase(BKey, BName) ), C AS ( SELECT *, row_number() OVER ( PARTITION BY CKey ORDER BY CKey ) AS rn FROM ( VALUES (1, 'detail c head 1'), (1, 'detailc c head 2') ) AS CBase(CKey, CName) ), JN AS ( SELECT Bkey AS JKey, rn FROM B UNION SELECT Ckey AS JKey, rn FROM C ) SELECT * FROM ( VALUES (1, 'head 1'), (2, 'head 2') ) AS A(AKey, AName) LEFT JOIN JN ON JN.JKey = A.AKey LEFT JOIN C ON JN.JKey = C.CKey AND JN.rn = C.rn LEFT JOIN B ON JN.JKey = B.BKey AND JN.rn = B.rn