365 Days of SQL: Day 153

If you are reading this, thank you for dropping by. I hope so far my blogs have been motivational for you to keep rocking your journey. I hope you get whatever is it that you are hoping and wishing for.

Puzzle #38: Reporting Elements

I tried the solution in the live oracle environment here.

WITH RegionSales AS (
SELECT 'North' AS Region, 'ACE' AS Distributor, 10 AS Sales FROM DUAL UNION ALL
SELECT 'South' AS Region, 'ACE' AS Distributor, 67 AS Sales FROM DUAL UNION ALL
SELECT 'East' AS Region, 'ACE' AS Distributor, 54 AS Sales FROM DUAL UNION ALL
SELECT 'North' AS Region, 'Direct Parts' AS Distributor, 8 AS Sales FROM DUAL UNION ALL
SELECT 'South' AS Region, 'Direct Parts' AS Distributor, 7 AS Sales FROM DUAL UNION ALL
SELECT 'West' AS Region, 'Direct Parts' AS Distributor, 12 AS Sales FROM DUAL UNION ALL
SELECT 'North' AS Region, 'ACME' AS Distributor, 65 AS Sales FROM DUAL UNION ALL
SELECT 'South' AS Region, 'ACME' AS Distributor, 9 AS Sales FROM DUAL UNION ALL
SELECT 'East' AS Region, 'ACME' AS Distributor, 1 AS Sales FROM DUAL UNION ALL
SELECT 'West' AS Region, 'ACME' AS Distributor, 7 AS Sales FROM DUAL
), cte_DistinctRegion AS
(
SELECT	DISTINCT Region
FROM	RegionSales
),
cte_DistinctDistributor AS
(
SELECT	DISTINCT Distributor
FROM	RegionSales
),
cte_CrossJoin AS
(
SELECT	Region, Distributor
FROM	cte_DistinctRegion a CROSS JOIN
		cte_DistinctDistributor b
)
SELECT	a.Region, a.Distributor, NVL(b.Sales,0) AS Sales
FROM	cte_CrossJoin a LEFT OUTER JOIN
		RegionSales b ON a.Region = b.Region and a.Distributor = b.Distributor
ORDER BY a.Distributor,
		(CASE a.Region	WHEN 'North' THEN 1 
						WHEN 'South' THEN 2 
						WHEN 'East'	 THEN 3 
						WHEN 'West'	 THEN 4 END);