基于客户/门店数据对销售数据进行逆透视转换的技术问询

要将你的宽表格式销售数据转换为适合Tableau的窄表结构，可以通过以下SQL方法实现：

通用解决方案（适用于所有数据库） #

使用UNION ALL将各产品列逐一拆分，合并为单列的产品与对应数量：

SELECT Customer, Store, 'Baked Goods' AS Product, [Baked Goods] AS Count
FROM [TABLE]
UNION ALL
SELECT Customer, Store, 'Meat Counter' AS Product, [Meat Counter] AS Count
FROM [TABLE]
UNION ALL
SELECT Customer, Store, 'Canned Goods' AS Product, [Canned Goods] AS Count
FROM [TABLE]
ORDER BY Customer, Product;

特定数据库简化方案 #

如果使用支持UNPIVOT语法的数据库（如SQL Server），可以用更简洁的写法：

SELECT Customer, Store, Product, Count
FROM [TABLE]
UNPIVOT
(
  Count FOR Product IN ([Baked Goods], [Meat Counter], [Canned Goods])
) AS UnpivotTable
ORDER BY Customer, Product;

转换后的数据结构 #

执行上述SQL后，将得到你期望的窄表结构：

内容的提问来源于stack exchange，提问作者help_help_help