sql - 如何使用列变量作为列名

Question

我目前有以下查询:

SELECT a.instance_type, SUM(a.quantity) as quantity, b.name, b.id
FROM sales_iteminstance a
INNER JOIN inventory_item b ON b.id = a.fk_item_id
GROUP BY (a.instance_type, b.id)
ORDER BY (b.id)

哪个返回:

+---------------+----------+----------+----+
| instance_type | quantity |   name   | id |
+---------------+----------+----------+----+
| Sell          |        5 | Gas 50Kg |  5 |
| Buy           |        8 | Gas 50Kg |  5 |
| Return        |        4 | Gas 50Kg |  5 |
+---------------+----------+----------+----+

是否可以更新查询以压缩上面的表格并使用 instance_type 作为列名？即

+----+----------+------+-----+--------+
| id |   name   | sell | buy | return |
+----+----------+------+-----+--------+
|  5 | Gas 50Kg |    5 |   8 |      4 |
+----+----------+------+-----+--------+

Answer 1

是的，一个简单的数据透视查询就可以做到这一点:

SELECT
    id,
    name,
    MAX(CASE WHEN instance_type = 'Sell'   THEN quantity END) AS sell,
    MAX(CASE WHEN instance_type = 'Buy'    THEN quantity END) AS buy,
    MAX(CASE WHEN instance_type = 'Return' THEN quantity END) AS "return"
FROM yourTable
GROUP BY
    id, name;