我能用SQL制作枢轴表吗？

Question

我有这样的数据：

│ Row │ variable │ value │ Customer │ location │
├─────┼──────────┼───────┼──────────┼──────────┤
│ 1   │ January  │ 5     │ x        │ X1       │
│ 2   │ January  │ 3     │ x        │ X2       │
│ 3   │ January  │ 6     │ y        │ X3       │
│ 4   │ February │ 4     │ x        │ X1       │
│ 5   │ February │ 3     │ x        │ X2       │
│ 6   │ February │ 3     │ y        │ X3       │
│ 7   │ March    │ 5     │ x        │ X1       │
│ 8   │ March    │ 4     │ x        │ X2       │
│ 9   │ March    │ 4     │ y        │ X3       │
│ 10  │ April    │ 0     │ x        │ X1       │
│ 11  │ April    │ 2     │ x        │ X2       │
│ 12  │ April    │ 2     │ y        │ X3       │
│ 13  │ May      │ 3     │ x        │ X1       │
│ 14  │ May      │ 5     │ x        │ X2       │
│ 15  │ May      │ 5     │ y        │ X3       │
│ 16  │ June     │ 2     │ x        │ X1       │
│ 17  │ June     │ 1     │ x        │ X2       │
│ 18  │ June     │ 7     │ y        │ X3       │

我可以创建一个SQL从它生成一个枢轴表，如下所示：

│ Row │ Customer │ January │ February │ March │ April │ May   │ June  │
├─────┼──────────┼─────────┼──────────┼───────┼───────┼───────┼───────┤
│ 1   │ x        │ 8       │ 7        │ 9     │ 2     │ 8     │ 3     │
│ 2   │ y        │ 6       │ 3        │ 4     │ 2     │ 5     │ 7     │

Answer 1

使用条件聚合：

SELECT
    customer,
    SUM(CASE WHEN variable = 'January' THEN value ELSE 0 END) January,
    SUM(CASE WHEN variable = 'February' THEN value ELSE 0 END) February,
    SUM(CASE WHEN variable = 'March' THEN value ELSE 0 END) March,
    SUM(CASE WHEN variable = 'April' THEN value ELSE 0 END) April,
    SUM(CASE WHEN variable = 'May' THEN value ELSE 0 END) May,
    SUM(CASE WHEN variable = 'June' THEN value ELSE 0 END) June
FROM mytable
GROUP BY customer
ORDER BY customer

如果您确实需要第一列(基本上为记录分配新编号)，则可以使用ROW_NUMBER() (此功能仅在MySQL 8.0中可用)：

SELECT 
    ROW_NUMBER() OVER(ORDER BY customer) Row,
    x.*
FROM (
    SELECT
        customer,
        SUM(CASE WHEN variable = 'January' THEN value ELSE 0 END) January,
        SUM(CASE WHEN variable = 'February' THEN value ELSE 0 END) February,
        SUM(CASE WHEN variable = 'March' THEN value ELSE 0 END) March,
        SUM(CASE WHEN variable = 'April' THEN value ELSE 0 END) April,
        SUM(CASE WHEN variable = 'May' THEN value ELSE 0 END) May,
        SUM(CASE WHEN variable = 'June' THEN value ELSE 0 END) June
    FROM mytable
    GROUP BY customer
) x
ORDER BY customer