Alternatives to UNION ALL to get aggregate data

Question

What are alternatives to UNION ALL for joining many tables to see aggregated data from many source systems? I am looking for solution beyond T-SQL.

Suppose we have 3 source tables and we want to join them by id:

TableA

id  Adata
1   8383
2   2707
3   6181
4   6708

TableB

id  Bdata
1   2669
1   8582
2   6335
2   7355
3   7355
3   2277
4   2789
4   8566

TableC

id  Adata
1   2856
2   1364
3   4476
4   3311
4   8073

The desired result table I want to get:

The primitive way I do now to get it (by UNION ALL):

Reaching desired results by creation of huge table consisting of many tables under each other and many nulls seems to be very frustrating and primitive solution if we have many columns and many tables. I think this is common problem if we want to join different information systems like accounting, payroll, purchase etc.

The final aim is to give and Excel user aggregated data with a chance to drill down limited to selected table. For example by double clicking in desired results table in the first Bdata 11251 the user is intrested to see only the data that make that figure. But instead the user gets this:

Update. I want to preserve a chance for drill down in data. So I want to be able to see all details for each ID if I double click on results table in Excel.

Answer 1

With your example, a simple join seems to be enough, why would you need to UNION ALL ?

SELECT A.id,
    SUM(A.Adata),
    SUM(B.Bdata),
    SUM(C.Cdata)
FROM TableA A
    JOIN TableB B ON A.id = B.id
    JOIN TableC C ON A.id = C.id
GROUP BY A.id

If you want to show the overall total, you can use WITH ROLLUP:

SELECT ISNULL(sub.id,'Total') AS "id",
    sub.Adata,
    sub.Bdata,
    sub.Cdata
FROM (
    SELECT A.id,
        SUM(A.Adata) AS Adata,
        SUM(B.Bdata) AS Bdata,
        SUM(C.Cdata) AS Cdata
    FROM TableA A
        JOIN TableB B ON A.id = B.id
        JOIN TableC C ON A.id = C.id
    GROUP BY A.id WITH ROLLUP) sub