0

I am trying to convert rows to columns in postgres using crosstab or any other ways

Table 1:

Order_Id Order_line_id
1 1001
1 1
1 2

Table 2:

Order_Id Order_line_id Type Amount
1 1001 APPLE 60
1 1001 APPLE 90
1 1 APPLE 0
1 1 ORANGE 32
1 1 KIWI 45
1 2 APPLE 12
1 2 ORANGE 76
1 2 ORANGE 98

Result:

Order_Id Order_line_id APPLE1 APPLE2 ORANGE1 ORANGE2 KIWI1 KIWI2
1 1001 60 90 null null null null
1 1 0 null 32 null null 45
1 2 12 null 76 98 null null

Column names are known already but the column values might be duplicate and they should be go next to each other.

i tried hard with cross tab and json (atleast tried to bring in json) couldnt progress. any help pls?

I tried to transpose rows to columns but the columns values may be duplicate. duplicate values must still be in separate column. I tried to achieve in crosstab but it didnt work

rev gan
  • 45
  • 1
  • 4

1 Answers1

0

Your problem cannot be resolved with crosstab because the list of the columns in the result must be dynamically calculated against the rows of the Table 2 which may be updated at any time.

A solution exists to solve your problem. It consists of :

  1. creating a composite type dynamically with the list of expected column labels according to the Table 2 status within a plpgsql procedure
  2. calling the procedure before executing the query
  3. building the query by grouping the Table 2 rows by Order_id and Order_line_id so that to aggregate these rows into the taget row structure
  4. converting the target rows into json objects and displaying these json objects in the final status by using the json_populate_record function and the composite type

Step 1 :

CREATE OR REPLACE PROCEDURE composite_type() LANGUAGE plpgsql AS $$
DECLARE
  column_list text ;
BEGIN
SELECT string_agg(m.name || ' text', ', ')
  INTO column_list
  FROM
     ( SELECT Type || generate_series(1, max(count)) AS name
         FROM
            ( SELECT lower(Type) AS type, count(*)
                FROM table_2
               GROUP BY Order_Id, Order_line_id, Type
            ) AS s
        GROUP BY Type
     ) AS m ;

DROP type IF EXISTS composite_type ;
EXECUTE 'CREATE type composite_type AS (' || column_list || ')';
END ; $$

Step 2 :

CALL composite_type() ;

Step 3,4 :

SELECT t.Order_Id, t.Order_line_id
     , (json_populate_record(null :: composite_type, json_object_agg(t.label, t.Amount))).*
  FROM 
     ( SELECT Order_Id, Order_line_id
            , lower(Type) || (row_number() OVER (PARTITION BY Order_Id, Order_line_id, Type)) :: text AS label
            , Amount
         FROM table_2
     ) AS t
 GROUP BY t.Order_Id, t.Order_line_id ;

The final result is :

order_id order_line_id kiwi1 orange1 orange2 apple1 apple2
1 1 45 32 null 0 null
1 2 null 98 76 12 null
1 1001 null null null 60 90

see the full test result in dbfiddle

Edouard
  • 6,577
  • 1
  • 9
  • 20
  • thank you very much!! You really nailed it and solved my biggest issue!! I appreciate it!! – rev gan Nov 07 '22 at 23:46
  • how can i extend this for more columns like 'Amount'. Say there is new column 'Availability' , is it possible to write APPLE1_Amount, APPLE1_Availability, APPLE2_Amount, APPLE2_Availability? – rev gan Nov 08 '22 at 18:14
  • I am thinking to create new type and use in json_populate_record – rev gan Nov 08 '22 at 18:20
  • sure you can extend by enriching the composite type on one side and the query on the other side with both sides consistent together. – Edouard Nov 08 '22 at 18:42