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

Table 1:

Table 2:

Result:

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

CodePudding user response：

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 :

see the full test result in dbfiddle