如何在postgresql中将一个多行的表转换为一个一行多列的表？

Question

我有一个表，其中包含一些以行为单位的规格

product_id  |   spec      |   value   |
------------+-------------+-----------+
  prod-1    |   speed     |    30     |
  prod-1    |   weight    |    20     |
  prod-1    |   power     |    500    |
  prod-1    |   autonomy  |    20     |

我想创建一个查询或函数，将其返回为

product_id   | speed  | weight  | power | autonomy |
-------------+--------+---------+-------+----------+
  prod-1     |  30    |    20   |  500  |    20    |

为了在前端有更好的操控。

Answer 1

使用扩展tablefunc中的crosstab函数将行转换为列：

SELECT *
FROM crosstab(
 'SELECT product_id, spec, value FROM specs' 
) AS ct(product_id int, speed int,  weight int, power int, autonomy int);

演示：db<>fiddle

Answer 2

您可以使用条件聚合，它在Postgres中看起来很像：

select product_id,
       max(value) filter (where spec = 'weight') as weight,
       max(value) filter (where spec = 'speed') as speed,
       max(value) filter (where spec = 'power') as power,
       max(value) filter (where spec = 'autonomy') as autonomy
from specs s
group by product_id;