Create columns from distinct values of a column

Question

I have a large dataset with many different product. In a small scale it would look like this:

product	month	amount
AA	1	100
AA	1	150
AA	2	200
AA	2	120
BB	2	180
BB	2	220
CC	3	80

I want get the info in a different order. Write as many new columns as distinct values from the column "product", then fill it with the sum of the amount by month. It would look like this:

month	AA	BB	CC
1	250	NA	NA
2	320	400	NA
3	NA	NA	80

The important thing is to PIVOT the table, that's the main problem I have. I saw similar questions but all have being solved with PIVOT function but I'm doing this with a Postgres database in DBeaver and it doesn't have the PIVOT function:

SELECT *
FROM (
    SELECT product, month, amount
    FROM ventas
) AS SourceTable
PIVOT (
    SUM(amount)
    FOR month IN ([1], [2], [3])
) AS PivotTable;

SQL Error [42601]: ERROR: syntax error at or near "PIVOT" Position: 95

I've tried different ways but no success. I can't write the names of all products in the query as there are too many!

Here is a test setup for your convenience:

CREATE TABLE ventas (
    product VARCHAR(50),
    month INT,
    amount INT
);

INSERT INTO ventas (product, month, amount) VALUES 
('AA', 1, 100),
('AA', 1, 150),
('AA', 2, 200),
('AA', 2, 120),
('BB', 2, 180),
('BB', 2, 220),
('CC', 3, 80),

postgres has no command PIVOT use CROSSTAB instead see stackoverflow.com/questions/20618323/… — nbk
– nbk, Commented Mar 13, 2024 at 23:51
Thanks nbk. It's a good info, but in this case, how can I solve the fact that there are many unique values for the column and I can't type all of them? Because in this example and all that I checked they are written, and I have to create more than 100 columns — Francisco MS
– Francisco MS, Commented Mar 13, 2024 at 23:56
For unknown number of columns you almost always have to use dynamic sql. That being said, why can't you have result having 12 months as columns x n products as rows? — Salman Arshad
– Salman Arshad, Commented Mar 14, 2024 at 0:10
Do you actually need 100 separate output columns? Else, an array (for same data types) or a document type like json / jsonb / hstore / xml is much easier to handle ... — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 14, 2024 at 2:21

Erwin Brandstetter · Accepted Answer · 2024-03-14 02:43:39Z

1

If you actually need separate output columns, the only reasonable approach is a 2-step flow:

Generate the query dynamically.
Execute it.

If you are not familiar with the crosstab() function, read this first:

PostgreSQL Crosstab Query

-- generate crosstab() query
SELECT format(
$f$  -- begin dynamic query string
SELECT * FROM crosstab(
   $q$
   SELECT month, product, sum(amount)
   FROM   ventas
   GROUP  BY 1, 2
   ORDER  BY 1, 2
   $q$
 , $c$VALUES (%s)$c$
   ) AS ct(month int, %s);
$f$  -- end dynamic query string
            , string_agg(quote_literal(sub.product), '), (')
            , string_agg(quote_ident  (sub.product), ' int, ') || ' int'
                 )
FROM  (SELECT DISTINCT product FROM ventas ORDER BY 1) sub;

This generates a query of the form:

SELECT * FROM crosstab(
   $q$
   SELECT month, product, sum(amount)
   FROM   ventas
   GROUP  BY 1, 2
   ORDER  BY 1, 2
   $q$
 , $c$VALUES ('AA'), ('BB'), ('CC')$c$
   ) AS ct(month int, "AA" int, "BB" int, "CC" int);

... which you then execute.

fiddle

Closely related, with more explanation and options:

Pivot table using crosstab and count

answered Mar 14, 2024 at 2:43

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

4 Comments

Francisco MS Over a year ago

Thank you so much Erwin! Appreciate the fiddle tool, got it!

Francisco MS Over a year ago

But from what I understand, you always have to type the name of the columns of the product, and in my real case there are more than 100... any idea?

Erwin Brandstetter Over a year ago

@FranciscoMS: No you don't type any product names here. Postgres does that for you, that's the point.

Francisco MS Over a year ago

Thank you Erwin, understood all, thanks so much!!!

Collectives™ on Stack Overflow

Create columns from distinct values of a column

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related