Can Unnest filtered in PostgreSQL?-CodePudding

I have a table that has values where the "TAHUN" column group by date_part('year'::text, insert_date) and the value of 1000 in the column "TAHUN" as total

I want to transpose rows to columns like this (but the "TAHUN" column remains dynamic so I don't have to manually insert it at the turn of the year" :

I'm confused about filtering by column "TAHUN" with unnest. This is My Query :

SELECT
    unnest(array['PENDAPATAN', 'PENJUALAN 1', 'PENJUALAN 2', 'PENDAPATAN LAIN-LAIN', 'TOTAL PENDAPATAN', 'HARGA POKOK PENJUALAN (HPP)', 'PEMBELIAN', 'ONGKOS KIRIM']) AS "title",
    unnest(array["PENDAPATAN", "PENJUALAN 1", "PENJUALAN 2", "PENDAPATAN LAIN-LAIN", "TOTAL PENDAPATAN", "HARGA POKOK PENJUALAN (HPP)", "PEMBELIAN", "ONGKOS KIRIM"]) FILTER (WHERE "TAHUN" != 1000) AS "dynamic year",
    unnest(array["PENDAPATAN", "PENJUALAN 1", "PENJUALAN 2", "PENDAPATAN LAIN-LAIN", "TOTAL PENDAPATAN", "HARGA POKOK PENJUALAN (HPP)", "PEMBELIAN", "ONGKOS KIRIM"]) FILTER (WHERE "TAHUN" = 1000) AS "total"
FROM vw_blm_dashboard_hasil_usaha
GROUP BY "title", "dynamic year", "total";

Can unnest filtered? or Do you have any other way to solve this problem?

Thanks before

CodePudding user response：

You need to move the unnesting into the FROM clause, so that you get columns that you can reference.

I personally find writing this using a VALUES clause a bit easier to read:

SELECT
    x.title ,
    max(x.value) FILTER (WHERE v."TAHUN" = 2019) AS "2019",
    max(x.value) FILTER (WHERE v."TAHUN" = 2020) AS "2020",
    max(x.value) FILTER (WHERE v."TAHUN" = 2021) AS "2021",
    max(x.value) FILTER (WHERE v."TAHUN" = 1000) AS "total"
FROM vw_blm_dashboard_hasil_usaha v
 CROSS JOIN LATERAL (  
     values ("PENDAPATAN", 'PENDAPATAN'), 
            ("PENJUALAN 1", 'PENJUALAN 1'), 
            ("PENJUALAN 2", 'PENJUALAN 2'), 
            ... all other columns come here ...
 ) as x(value, title)
GROUP BY x.title;