PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Get data on different rows per ID on one row

My data looks like:

ID Year Volume1 Volume2
AAA 2023 10 20
AAA 2024 5 10
BBB 2023 20 40
BBB 2024 30 60

I would like to have the volumes per ID all on one row, where ideally the column names are extended with the year separated with an underscore sign.

What I want to have:

ID Volume1_2023 Volume2_2023 Volume1_2024 Volume2_2024
AAA 10 20 5 10
BBB 20 40 30 60

How can I do this in PostgreSQL?

Tags:

3

Answers


  1. 0

    First, enable the tablefunc extension (if it’s not already enabled).
    Then execute this query, replacing "your_table_name" with your actual table name:

    SELECT *
FROM crosstab(
    'SELECT ID, 
            CONCAT(''Volume1_'', Year) AS Volume1_Year, 
            Volume1, 
            CONCAT(''Volume2_'', Year) AS Volume2_Year, 
            Volume2
     FROM your_table_name
     ORDER BY ID, Year'
) AS ct(
    ID text, 
    Volume1_2023 int, 
    Volume2_2023 int, 
    Volume1_2024 int, 
    Volume2_2024 int
);
    Login or Signup to reply.
  2. 0

    The non cross tab pivot query approach here would be:

    SELECT
    ID,
    MAX(Volume1) FILTER (WHERE Year = 2023) AS Volume1_2023,
    MAX(Volume2) FILTER (WHERE Year = 2023) AS Volume2_2023,
    MAX(Volume1) FILTER (WHERE Year = 2024) AS Volume1_2024,
    MAX(Volume2) FILTER (WHERE Year = 2024) AS Volume2_2024
FROM yourTable
GROUP BY ID
ORDER BY ID;
    Login or Signup to reply.
  3. 0

    My suggestion is to aggregate years and volumes into a JSONB column.

    select id, 
    jsonb_object_agg('volume1_'||year, volume1) || 
    jsonb_object_agg('volume2_'||year, volume2) as volumes
from the_table
group by id;

    DB Fiddle Demo

    id volumes
    BBB {"volume1_2023": 20, "volume1_2024": 30, "volume2_2023": 40, "volume2_2024": 60}
    AAA {"volume1_2023": 10, "volume1_2024": 5, "volume2_2023": 20, "volume2_2024": 10}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search