0

Reproducing a question asked some time ago but this time using PostgreSQL:

I have a table that looks like this:

Month      Site          Val
2009-12    Microsoft      10
2009-11    Microsoft      12
2009-10    Microsoft      13
2009-12    Google         20
2009-11    Google         21
2009-10    Google         22

And I want to get a 2-dimension table that gives me the "Val" for each site's month, like:

Month      Microsoft      Google
2009-12        10           20
2009-11        12           21
2009-10        13           22

In my case the "Site" column has hundreds of values, which makes it very tedious to explicitly state their names and types (all are the same type) in crosstab function.

Any idea of how to do it in PostgreSQL?

Guillermo.D
  • 399
  • 2
  • 14
  • What were you going to do with the result? – Bergi Feb 03 '23 at 02:51
  • Save it as new table.... I'm reading about using "dynamic sql" to generate the column names and types required by the crosstab() function, but I'm still finding my way through the code. – Guillermo.D Feb 03 '23 at 03:21
  • 1
    You can't have a dynamic number of columns in a SQL query. A fundamental restriction of the SQL language is, that the number, names and data types of all columns of a query must be know to the database _before_ it starts retrieving data. This is true for all DBMS. Oracle and SQL Server that provide the PIVOT operator still require to spell out all columns - albeit in as much more compact way –  Feb 03 '23 at 08:27
  • 1
    Pivot is done better in the UI/Frontend anyways. SQL is simply not designed for that. You could aggregate the result into one JSON value for each month which might be easier for the frontend to work with. –  Feb 03 '23 at 08:29
  • If the number of sites doesn't change very often, you can dynamically create a view that does the pivot (e.g. using filtered aggregation). Something like this: https://stackoverflow.com/a/66744030 –  Feb 03 '23 at 08:30

1 Answers1

1

It's actually possible with the dynamic sql capability of Postgres!

DO $$
DECLARE
temp_cols text;
query text;

BEGIN
select string_agg(Site ||' int' , ', ')
into temp_cols
FROM (SELECT DISTINCT Site FROM my_tbl ORDER by Site) AS col_list;

query := 'create table temp_results as 
SELECT * FROM crosstab(
''SELECT Month, Site, Val FROM temp_tbl ORDER BY 1,2'', 
''SELECT DISTINCT Site FROM temp_tbl ORDER by Site'') 
AS ct(Month text, ' || temp_cols ||')';

EXECUTE query;
END $$;

And that's all needed...

Guillermo.D
  • 399
  • 2
  • 14