如何在不使用SQL中使用枢轴函数的情况下旋转？

发布于 2025-01-22 08:44:53 字数 717 浏览 0 评论 0原文

我想在不使用任何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   |

我将如何在不使用任何枢轴函数的情况下再次实现？

原文

I'd like to pivot a table without using any PIVOT function but using pure SQL only (let's say PostgreSQL syntax for example).

So having this data-set as an example:

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 |

I'd expect to pivot on the Order column and having somenthing like:

Order | Item | Quantity | TotalCost |
-------------------------------------
1     | Bread |    3    |    3,30   |
2     | Pizza |    2    |    10     |
3     | Pasta |    5    |    2,50   |

How would I achieve that, once again, without using any pivot function?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

花开柳相依 2025-01-29 08:44:53

解决方案A

使用语句时：

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

使用 self 左JOIN JOIN s：

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

后者的效率可能不那么高效，但是有用的用例可能有用。

Solution A

using CASE WHEN statements:

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

Solution B

Using self LEFT JOINs:

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

The latter might not be as much as efficient but there are use case in which might come useful.

回复收藏 0 原文

梦里寻她 2025-01-29 08:44:53

在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关键字，因此对于列名称是一个不好的选择。

In Postgres, you would use 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;

Note that ORDER is a SQL Keyword so it is a bad choice for a column name.

回复收藏 0 原文

~没有更多了~