PostgreSQL pivot on dynamic column

H3lltronik 0
Viewed 24

I have the following code

SELECT mu.id,
    u.pk AS fkgerente,
    u.u AS gerente,
    mu.meta,
    mu.meta_date::TEXT
FROM usuario u
    right JOIN metas_usuario mu on mu.user_id = u.pk
    join metas_type mt on mt.id = mu.meta_type_id 
WHERE u.del = 0
    AND u.fkp = '2453ff2c-6494-4a6d-a15f-f70384b669c1'
    AND mu.meta_date  BETWEEN SYMMETRIC '2022-08-27' AND '2022-09-24'
    AND mt.id = 4
ORDER BY gerente asc

It gives an output like this

enter image description here

I was wondering if there's anyway to use PIVOT to transpose dymanic columns such as dates (meta_date) to columns to have an output like this: enter image description here

When the data is handled by the API server before writting it into the DB it ensures it follows some specific rules on how to write meta_date so there will be always date to group on.

I wonder whether is possible to achieve this only using SQL or is it necessary to transpose it on the API.

pivot transpose sql postgresql
1 Answers

PostgreSQL has crosstab function that you can pivot your data in desired format. Read here: https://www.postgresql.org/docs/current/queries-with.html

But there is a sticky limitation here: output (pivoted) columns must be given explicitly . Because sql engine must know output format completely with data types.

Possibly you can write a function that first execute query and determine output format than create sql dynamically and execute

bekir çelik0
Related