Search code examples
postgresqlpivotcrosstab

Postgresql - Pivot with Crosstab or alternative?

I have the following table:

enter image description here

I want to pivot this and have the following result:

  Table_name        Column_name      Values         ddmf_etl_id
"PROFITCENTERID"    "CO_AREA"       "DE02"           16807487
"PROFITCENTERID"    "PROFIT_CTR"    "0000001119"     16807487
"PROFITCENTERID"    "CO_AREA"       "DE02"           16807488
"PROFITCENTERID"    "PROFIT_CTR"    "0000001120"     16807488

Unfortunately I have failed to do this with the following select as CROSSTAB has a limited number (3) of arguments:

SELECT  'PROFITCENTERID' AS table_name,t.*
FROM   

crosstab(

        'SELECT unnest(''{CO_AREA,PROFIT_CTR}''::text[]) AS col
             , row_number() OVER ()
             , unnest(ARRAY[CO_AREA::text,PROFIT_CTR::text]) AS val
        FROM   "1".PROFITCENTER_PROFITCENTERID'

   ) t (column_name text,  values text);

Result of the select statement (wrong) :

enter image description here

Are there other alternatives to Crosstab in Postgresql? Or maybe is it possible to do this with Crosstab?

Any help is appreciated! Thank you!

Solution

  • Use unnest() to double each row and select appropriate values:

    select
    'profitcenterid' as table_name,
    unnest(array['co_area', 'profit_ctr']) as column_name,
    unnest(array[co_area, profit_ctr]) as value,
    ddmf_etl_id as ddmf_etl_id
from profitcenter_profitcenterid

   table_name   | column_name |   value    | ddmf_etl_id 
----------------+-------------+------------+-------------
 profitcenterid | co_area     | de02       |    16807487
 profitcenterid | profit_ctr  | 0000001119 |    16807487
 profitcenterid | co_area     | de02       |    16807488
 profitcenterid | profit_ctr  | 0000001120 |    16807488
(4 rows)

    Live demo in rextester.

    However, this is not much help to get the expected json result. You want to get arbitrary key names, so there is no way to automatically generate the objects based on a subquery result. They can be created with the primitive jsonb_build_object():

    select to_jsonb(s)
from (
    select
        'profitcenterid' as table_name,
        17 as type,
        '' as value,
        jsonb_build_array(
            jsonb_build_object('name', 'co_area', 'value', co_area, 'type', 0, 'children', '[]'::jsonb),
            jsonb_build_object('name', 'profit_ctr', 'value', profit_ctr, 'type', 0, 'children', '[]'::jsonb)
        ) as children
    from profitcenter_profitcenterid
    ) s

    Example in rextester.

    As a side note, the expected structure seems unnecessarily complex, simplicity is a virtue.