Hi,
Our Query is below
WITH
v1 AS
(SELECT 1 AS id, 4 AS exc_attr, null as t_id, 8 as revenue FROM dual UNION ALL
SELECT 2 AS id, 40 AS exc_attr, null as t_id, 6 as revenue FROM dual UNION ALL
SELECT 3 AS id, 160 AS exc_attr, null as t_id, 10 as revenue FROM dual
),
v2 as
(SELECT 1 AS id, null AS exc_attr, 101 as t_id FROM dual UNION ALL
SELECT 1 AS id, null AS exc_attr, 102 as t_id FROM dual UNION ALL
SELECT 1 AS id, null AS exc_attr, 103 as t_id FROM dual UNION ALL
SELECT 2 AS id, null AS exc_attr, 201 as t_id FROM dual UNION ALL
SELECT 2 AS id, null AS exc_attr, 202 as t_id FROM dual UNION ALL
SELECT 3 AS id, null AS exc_attr, 301 as t_id FROM dual UNION ALL
SELECT 3 AS id, null AS exc_attr, 302 as t_id FROM dual UNION ALL
SELECT 3 AS id, null AS exc_attr, 303 as t_id FROM dual UNION ALL
SELECT 4 AS id, null AS exc_attr, 401 as t_id FROM dual UNION ALL
SELECT 5 AS id, null AS exc_attr, 501 as t_id FROM dual
)
,
combined_data (id, exc_attr, vertical) AS
(
SELECT id, exc\_attr, 1 FROM v1 UNION ALL
SELECT id, NULL, 2 FROM v2
)
, got_exc_n AS
(
SELECT MAX ( CASE
WHEN exc\_attr = 0
THEN 1
END
) OVER (PARTITION BY id) AS exc\_0
, MAX ( CASE
WHEN exc\_attr = 40
THEN 1
END
) OVER (PARTITION BY id) AS exc\_1
, vertical
FROM combined\_data
)
SELECT SUM (v_1) AS vertical_1
, SUM (v_2) AS vertical_2
, NVL (dscr, 'Total') AS description
FROM got_exc_n
UNPIVOT INCLUDE NULLS ( exc_val
FOR dscr IN ( exc\_0 AS 'Exc 0'
, exc\_1 AS 'Exc 1'
)
)
PIVOT ( COUNT (exc_val)
FOR vertical IN ( 1 AS v\_1
, 2 AS v\_2
)
)
GROUP BY ROLLUP (dscr)
ORDER BY dscr
;
And produces below output:
But we are looking for below output, Please assist:
