sql-server - 如何在查询中获取列名中的列数据？

Question

如果我有下表:

 Cust Prod     Qty
===== ====     ===
 Bob   Apple    3
 Bob   Orange   2
 Bob   Banana   4
 Rob   Apple    2
 Rob   Orange   1
 Bob   Apple    2

如何将表数据作为列名得到如下结果:

Prod   Bob Rob
====== === ===
Apple    5   2
Orange   2   1
Banana   4  null

Answer 1

您可以使用 PIVOT在 MSSQL 或以下方式:

SELECT
PROD,
SUM(CASE WHEN Cust='Bob' THEN Qty ELSE 0 END) as Bob,
SUM(CASE WHEN Cust='Rob' THEN Qty ELSE 0 END) as Rob

FROM T
GROUP BY PROD

SQLFiddle demo