1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
| WITH tbl AS
(SELECT 'p1' AS product, 1 AS dpt, 11 AS sales FROM dual UNION ALL
SELECT 'p1' AS product, 1 AS dpt, 11 AS sales FROM dual UNION ALL
SELECT 'p2' AS product, 1 AS dpt, 11 AS sales FROM dual UNION ALL
SELECT 'p2' AS product, 2 AS dpt, 11 AS sales FROM dual UNION ALL
SELECT 'p3' AS product, 2 AS dpt, 11 AS sales FROM dual UNION ALL
SELECT 'p3' AS product, 3 AS dpt, 11 AS sales FROM dual
),
reg_dim AS
(SELECT 'region_1' AS reg, 1 AS dpt FROM dual UNION ALL
SELECT 'region_1' AS reg, 2 AS dpt FROM dual UNION ALL
SELECT 'region_2' AS reg, 3 AS dpt FROM dual
)
SELECT product,
SUM(DECODE ( reg, 'region_1', sales, 0) ) AS REGION_1,
SUM(DECODE ( reg, 'region_2', sales, 0) ) AS REGION_2
FROM tbl, reg_dim
WHERE tbl.dpt = reg_dim.dpt
GROUP BY product |
Partager