postgresql Pivot Table

Question

I have this type of table and I'd like to convert:

This kind of table:

customer_name   product_type    total_paid
   Lian             car            100
   Lian          motorbike         200
   Carl             car            300
   Carl          motorbike         500

In this other kind of table:

customer_name   car  motorbike
    Lian        100     200
    Carl        300     500

Using Postgresql, I would really appreciate if someone can help me please.

Hogan · Accepted Answer · 2022-07-06 20:58:20Z

1

You can use a group by and the SUM aggregate function.

SELECT customer_name,
       SUM(CASE WHEN product_type='car' THEN total_paid ELSE 0 END) as car,
       SUM(CASE WHEN product_type='motorbike' THEN total_paid ELSE 0 END) as motorbike
FROM tableyoudidnotname
GROUP BY customer_name

answered Jul 6, 2022 at 20:58

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

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

Comments

lsanchezo · Accepted Answer · 2022-07-06 20:55:10Z

0

Try this:

select customer_name, 
(select sum(total_paid) 
from testTable
where customer_name=T.customer_name and product_type='car') as car, 
(select sum(total_paid) 
from testTable
where customer_name=T.customer_name and product_type='motorbike') as motorbike
from testTable as T
group by customer_name
order by customer_name desc

Test in DBFiddle and replace the table 'testTable' with your table name.

answered Jul 6, 2022 at 20:55

lsanchezo

1348 bronze badges

1 Comment

Hogan Over a year ago

why do 3 queries when 1 will do?

Collectives™ on Stack Overflow

postgresql Pivot Table

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related