Amalgamating SQL queries stored as views together / Combining tables

Question

I have several summary queries stored as Views...

...and would like to join them together into one combined output as follows:

..so I can use it as a pivot table in Excel.

Date is the only common denominator in the case.

I can do this in Excel using SUMIFS but would prefer to manage it in the SQL before it arrives in Excel.

Can anyone help?

Answer 1

Without a matching ID, the best I can think of is to order by ROW_NUMBER(), which gives a slightly verbose query;

WITH cte1 AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY DATE 
                               ORDER BY CASE WHEN Dogs IS NULL THEN 1 END) r1 
  FROM View1
), cte2 AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY DATE 
                               ORDER BY CASE WHEN Region IS NULL THEN 1 END) r2
  FROM View2
), cte3 AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY DATE 
                               ORDER BY CASE WHEN Bed IS NULL THEN 1 END) r3
  FROM View3
)
SELECT COALESCE(cte1.Date, cte2.Date, cte3.Date) Date, 
                Dogs, D_Qty, Region, R_Qty, Bed, B_Qty
FROM cte1
FULL OUTER JOIN cte2
  ON cte1.Date = cte2.Date AND r1=r2
FULL OUTER JOIN cte3
  ON cte1.Date = cte3.Date AND r1=r3
  OR cte2.Date = cte3.Date AND r2=r3
ORDER BY Date, COALESCE(r1,r2,r3)

An SQLfiddle to test with.

You may consider adding an order column to your views, using ROW_NUMBER() OVER (PARTITION BY DATE ORDER BY (whatever order is in them), that would eliminate all the cte's and give you a stable ordering of things.