PIVOT using PostgreSQL

Question

I'm having trouble doing a pivot in PostgreSQL 12. I have to work with a table which is constructed awkwardly. This is a similar table to what I created (just for simple representaion)-

CREATE TABLE test(Product_num INT, SN INT, Attribute VARCHAR(100), Value DECIMAL, Note VARCHAR(50) );

The next step was to import a CSV file using the COPY function-

COPY public.test from 'C:\File Location\test.csv' DELIMITER ',' csv HEADER;

I get a table with more than 30k rows which looks like-

Product_num |    SN    |   Attribute   |   Value   |  Note  |
    100         9225       Unit sold       50          USA
    100         9225       Unit price      4.99
    100         9225       Num_boxes       2.5
    101         9226       Unit sold       1           GER
    101         9226       Unit price      920
    101         9226       Num_boxes       2

I want to get a table which is like the next table-

Product_num |    SN    |   Unit Sold   |   Unit price   |  Num_boxes |   Note
    100     |    9225  |        50     |       4.99     |    2.5     |   USA           
    101     |    9226  |        1      |       920      |     2      |   GER

I tried several methods including crosstab() and also tried to aggregate few of the columns but I encountered some issues. The table has some issues that need to be taken into consideration-

The attribute column is not identical for every SN- Meaning every SN has different attributes, some identical and some not.
There are a lot of null values.
There are a lot of 0 values.

I hope I managed to explain myself as good as possible Thanks

user330315 · Accepted Answer · 2020-08-18 10:55:41Z

3

I would aggregate all attributes into a JSON structure and then extract them in the final query:

select product_num, sn, 
       (attributes ->> 'Num_boxes')::decimal as num_boxes,
       (attributes ->> 'Unit sold')::decimal as unit_sold,
       (attributes ->> 'Unit price')::decimal as unit_price,
       note
from  (
  select product_num, sn, max(note) as note,
         jsonb_object_agg(attribute, value) as attributes
  from test
  group by product_num, sn
) t
order by product_num, sn;

If new attributes are added, you will need to extend the outer SELECT list to reflect that. There is no way you can have a dynamic list of columns without changing the query. In SQL the number and types of columns of a query must be known before running the statement.

answered Aug 18, 2020 at 10:55

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

NsN Over a year ago

It's a good solution, Thank you! I have around 40 attributes, so it is not a problem to add more manually

Akhilesh Mishra · Accepted Answer · 2020-08-18 11:13:04Z

1

I will suggest in this way:

First run below query:

select distinct (attribute) from test;

You will get all the attributes from above query:

Now add all attributes in below mentioned query to get your result:

select 
product_num,
sn,
coalesce(max(value) filter (where attribute ='Unit sold'),0) as "Unit Sold",
coalesce(max(value) filter (where attribute ='Unit price'),0) as "Unit Price",
coalesce(max(value) filter (where attribute ='Num_boxes'),0) as "Num_boxes",
.
.

coalesce(max(note),'')

from test

group by 1,2

you can use ilike also in filter condition if you want to match the values ignoring case.

answered Aug 18, 2020 at 11:13

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

Collectives™ on Stack Overflow

PIVOT using PostgreSQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related