WITH statq1 AS (
SELECT a.BUNKE_ID, b.BUNKENAVN, "naju", "kani", "mada", "asbi", "megh", "limo", "dool", "elmo", "makv", "haho", "chbh", "alal", "sibj", "miho", "juat", "kapo", "loje", "beni", "mean" FROM (
SELECT * FROM (
SELECT * FROM DSS_MAMA
UNPIVOT INCLUDE NULLS ( BUNKE_COUNT FOR BUNKE_ID IN (
bunke_id_4206,
bunke_id_4207,
bunke_id_4208,
bunke_id_4210,
bunke_id_4243,
bunke_id_4238,
bunke_id_4217,
bunke_id_4032,
bunke_id_4213,
bunke_id_4214,
bunke_id_4215,
bunke_id_4216,
bunke_id_4221,
bunke_id_4222,
bunke_id_4223,
bunke_id_4224,
bunke_id_4225,
bunke_id_4226,
bunke_id_4227,
bunke_id_4228,
bunke_id_4219,
bunke_id_4220,
bunke_id_4234,
bunke_id_4237,
bunke_id_4241,
bunke_id_4261,
bunke_id_4211,
bunke_id_4233,
bunke_id_4209,
bunke_id_4239,
bunke_id_4240,
bunke_id_2405,
bunke_id_2423,
bunke_id_3497,
bunke_id_4005,
bunke_id_4006,
bunke_id_4012,
bunke_id_4100,
bunke_id_4101,
bunke_id_4170,
bunke_id_4172,
bunke_id_4174,
bunke_id_4191,
bunke_id_4192,
bunke_id_4193,
bunke_id_4273,
bunke_id_4357,
bunke_id_4358,
bunke_id_4359,
bunke_id_4360
)))
PIVOT (SUM(nvl(bunke_count,0)) FOR BRUGERE IN ('naju' AS "naju", 'kani' AS "kani", 'mada' AS "mada", 'asbi' AS "asbi", 'megh' AS "megh", 'limo' AS "limo", 'dool' AS "dool", 'elmo' AS "elmo", 'makv' AS "makv", 'haho' AS "haho", 'chbh' AS "chbh", 'alal' AS "alal", 'sibj' AS "sibj", 'miho' AS "miho", 'juat' AS "juat", 'kapo' AS "kapo", 'loje' AS "loje", 'beni' AS "beni", 'mean' AS "mean"))) a
LEFT JOIN BUNKER b ON LTRIM(a.BUNKE_ID, 'BUNKE_ID_') = b.BUNKE_ID
--ORDER BY a.BUNKE_ID DESC
)
,
statq2 AS (SELECT a.BUNKE_ID, b.BUNKENAVN, "legr", "salu", "chba", "jath", "emjo", "mama" FROM (
SELECT * FROM (
SELECT * FROM DSS_MAMA
UNPIVOT INCLUDE NULLS ( BUNKE_COUNT FOR BUNKE_ID IN (
bunke_id_4206,
bunke_id_4207,
bunke_id_4208,
bunke_id_4210,
bunke_id_4243,
bunke_id_4238,
bunke_id_4217,
bunke_id_4032,
bunke_id_4213,
bunke_id_4214,
bunke_id_4215,
bunke_id_4216,
bunke_id_4221,
bunke_id_4222,
bunke_id_4223,
bunke_id_4224,
bunke_id_4225,
bunke_id_4226,
bunke_id_4227,
bunke_id_4228,
bunke_id_4219,
bunke_id_4220,
bunke_id_4234,
bunke_id_4237,
bunke_id_4241,
bunke_id_4261,
bunke_id_4211,
bunke_id_4233,
bunke_id_4209,
bunke_id_4239,
bunke_id_4240,
bunke_id_2405,
bunke_id_2423,
bunke_id_3497,
bunke_id_4005,
bunke_id_4006,
bunke_id_4012,
bunke_id_4100,
bunke_id_4101,
bunke_id_4170,
bunke_id_4172,
bunke_id_4174,
bunke_id_4191,
bunke_id_4192,
bunke_id_4193,
bunke_id_4273,
bunke_id_4357,
bunke_id_4358,
bunke_id_4359,
bunke_id_4360
)))
PIVOT (SUM(nvl(bunke_count,0)) FOR BRUGERE IN ('legr' AS "legr", 'salu' AS "salu", 'chba' AS "chba", 'jath' AS "jath",'emjo' AS "emjo", 'mama' AS "mama"))) a
LEFT JOIN BUNKER b ON LTRIM(a.BUNKE_ID, 'BUNKE_ID_') = b.BUNKE_ID
--ORDER BY a.BUNKE_ID DESC
)

SELECT a.*, b."legr", b."salu", b."chba", b."jath", b."emjo", b."mama" FROM statq1 a
LEFT JOIN statq2 b ON a.BUNKE_ID = b.BUNKE_ID
--ORDER BY a.BUNKENAVN ASC