我想在不使用任何PIVOT
函数的情况下透视表,但只使用纯SQL(例如PostgreSQL语法(。
以这个数据集为例:
Order | Element | Value |
---------------------------
1 | Item | Bread |
1 | Quantity | 3 |
1 | TotalCost | 3,30 |
2 | Item | Pizza |
2 | Quantity | 2 |
2 | TotalCost | 10 |
3 | Item | Pasta |
3 | Quantity | 5 |
3 | TotalCost | 2,50 |
我希望以Order
专栏为中心,并有一些类似的内容:
Order | Item | Quantity | TotalCost |
-------------------------------------
1 | Bread | 3 | 3,30 |
2 | Pizza | 2 | 10 |
3 | Pasta | 5 | 2,50 |
如果不使用任何枢轴功能,我将如何再次实现这一点?
解决方案A
使用CASE WHEN
语句:
SELECT Order,
MAX(CASE WHEN Element = 'Item' THEN Value END) AS Item,
MAX(CASE WHEN Element = 'Quantity' THEN Value END) AS Quantity,
MAX(CASE WHEN Element = 'TotalCost' THEN Value END) AS TotalCost
FROM MyTable
GROUP BY 1
解决方案B
使用自左联接:
SELECT A.Order, A.Item, B.Quantity, C.TotalCost
FROM
(SELECT Order, Value as Item
FROM MyTable
WHERE Element = 'Item') as A
LEFT JOIN
(SELECT Order, Value as Quantity
FROM MyTable
WHERE Element = 'Quantity') as B ON A.Order = B.Order
LEFT JOIN
(SELECT Order, Value as TotalCost
FROM MyTable
WHERE Element = 'TotalCost') as C ON B.Order = C.Order
后者可能没有那么有效,但在某些用例中可能会有用。
在Postgres中,您将使用FILTER
:
SELECT Order,
MAX(Value) FILTER (WHERE Element = 'Item') AS Item,
MAX(Value) FILTER (WHERE Element = 'Quantity') AS Quantity,
MAX(Value) FILTER (WHERE Element = 'TotalCost') AS TotalCost
FROM MyTable
GROUP BY 1;
请注意,ORDER
是一个SQL关键字,因此对于列名来说,这是一个错误的选择。