简单的 PIVOT UNPIVOT（T-SQL）

下面是一个简单的示例，显示每个工作日每个项目的平均项目价格。

首先，假设我们有一个表格，可以记录所有商品的价格。

CREATE TABLE tbl_stock(item NVARCHAR(10), weekday NVARCHAR(10), price INT);

INSERT INTO tbl_stock VALUES 
('Item1', 'Mon', 110), ('Item2', 'Mon', 230), ('Item3', 'Mon', 150), 
('Item1', 'Tue', 115), ('Item2', 'Tue', 231), ('Item3', 'Tue', 162), 
('Item1', 'Wed', 110), ('Item2', 'Wed', 240), ('Item3', 'Wed', 162), 
('Item1', 'Thu', 109), ('Item2', 'Thu', 228), ('Item3', 'Thu', 145), 
('Item1', 'Fri', 120), ('Item2', 'Fri', 210), ('Item3', 'Fri', 125),
('Item1', 'Mon', 122), ('Item2', 'Mon', 225), ('Item3', 'Mon', 140),
('Item1', 'Tue', 110), ('Item2', 'Tue', 235), ('Item3', 'Tue', 154),
('Item1', 'Wed', 125), ('Item2', 'Wed', 220), ('Item3', 'Wed', 142);

该表应如下所示：

+========+=========+=======+
|   item | weekday | price |
+========+=========+=======+
|  Item1 |    Mon  |   110 |
+--------+---------+-------+
|  Item2 |    Mon  |   230 |
+--------+---------+-------+
|  Item3 |    Mon  |   150 |
+--------+---------+-------+
|  Item1 |    Tue  |   115 |
+--------+---------+-------+
|  Item2 |    Tue  |   231 |
+--------+---------+-------+
|  Item3 |    Tue  |   162 |
+--------+---------+-------+
|          . . .           |
+--------+---------+-------+
|  Item2 |    Wed  |   220 |
+--------+---------+-------+
|  Item3 |    Wed  |   142 |
+--------+---------+-------+

为了执行聚合以查找每个工作日的每个项目的平均价格，我们将使用关系运算符 PIVOT 将表值表达式的列 weekday 旋转为聚合行值，如下所示：

SELECT * FROM tbl_stock
PIVOT ( 
    AVG(price) FOR weekday IN ([Mon], [Tue], [Wed], [Thu], [Fri])
) pvt;

结果：

+--------+------+------+------+------+------+
|  item  |  Mon |  Tue |  Wed |  Thu |  Fri |
+--------+------+------+------+------+------+
|  Item1 |  116 |  112 |  117 |  109 |  120 |
|  Item2 |  227 |  233 |  230 |  228 |  210 |
|  Item3 |  145 |  158 |  152 |  145 |  125 |
+--------+------+------+------+------+------+

最后，为了执行 PIVOT 的反向操作，我们可以使用关系运算符 UNPIVOT 将列旋转为行，如下所示：

SELECT * FROM tbl_stock
PIVOT ( 
    AVG(price) FOR weekday IN ([Mon], [Tue], [Wed], [Thu], [Fri])
) pvt
UNPIVOT ( 
    price FOR weekday IN ([Mon], [Tue], [Wed], [Thu], [Fri])
) unpvt;

结果：

+=======+========+=========+
|  item |  price | weekday |
+=======+========+=========+
| `Item1` |    116 |     Mon |
+-------+--------+---------+
| `Item1` |    112 |     Tue |
+-------+--------+---------+
| `Item1` |    117 |     Wed |
+-------+--------+---------+
| `Item1` |    109 |     Thu |
+-------+--------+---------+
| `Item1` |    120 |     Fri |
+-------+--------+---------+
| `Item2` |    227 |     Mon |
+-------+--------+---------+
| `Item2` |    233 |     Tue |
+-------+--------+---------+
| `Item2` |    230 |     Wed |
+-------+--------+---------+
| `Item2` |    228 |     Thu |
+-------+--------+---------+
| `Item2` |    210 |     Fri |
+-------+--------+---------+
| `Item3` |    145 |     Mon |
+-------+--------+---------+
| `Item3` |    158 |     Tue |
+-------+--------+---------+
| `Item3` |    152 |     Wed |
+-------+--------+---------+
| `Item3` |    145 |     Thu |
+-------+--------+---------+
| `Item3` |    125 |     Fri |
+-------+--------+---------+