postgresql Pivot Table-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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.