Pivot table in PostgreSQL repeating values in columns

Question

Having the following data in a table:
ID --------- Category --------- Value
1234 -------- Cat01 ----------- V001
1234 -------- Cat02 ----------- V002
1234 -------- Cat03 ----------- V003
1234 -------- Cat03 ----------- V004
1234 -------- Cat03 ----------- V005

I want to have the following output:

ID --------- Cat01 --------- Cat02 --------- Cat03
1234 ------- V001 ---------- V002 ---------- V003
1234 ------- V001 ---------- V002 ---------- V004
1234 ------- V001 ---------- V002 ---------- V005

How can it be done in PostgreSQL. As you can see, the value in Cat01 and Cat02 columns are repeated for each entry in Cat03 column
Many thanks for your help!

I have more than 3 categories but the number is fixed, is not dynamic. I have a total of 10 categories. Some categories can have more than one value (like in the example with Cat03) and, for those categories that have one value, repeat the value on the categories that have multiple values — Aleps
– Aleps, Commented Apr 12, 2016 at 17:07

David Aman · Accepted Answer · 2016-04-12 17:28:41Z

1

How about something like this:

SELECT a.val AS cat01, b.val AS cat02, c.val AS cat03
FROM
    test_pivot AS a,
    test_pivot AS b,
    test_pivot AS c
WHERE
    a.category = 'Cat01'
    AND
    b.category = 'Cat02'
    AND
    c.category = 'Cat03'

answered Apr 12, 2016 at 17:28

David Aman

3911 silver badge8 bronze badges

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

2 Comments

Aleps Over a year ago

If I dont filter by ID, it will do all combinations and will have more values, right ??

David Aman Over a year ago

Yes. I wasn't sure what you meant to do with ID, since your example only has a single value for it.

Collectives™ on Stack Overflow

Pivot table in PostgreSQL repeating values in columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related