PostgreSQL枢轴表

Question

我有这种类型的桌子，我想转换：

这种表：

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

在另一个表格中：

customer_name   car  motorbike
    Lian        100     200
    Carl        300     500

使用PostgreSQL，如果有人可以帮助我，我真的很感激。

Answer 1

您可以使用组和总和函数。

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

Answer 2

尝试以下操作：

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

在