深入浅出MSSQL交叉表

1. 什么是交叉表？

交叉表（Crosstab），也叫数据透视表（Pivot Table），是一种将行数据转换为列数据的技术，用于对数据进行分类汇总和展示。交叉表可以将一组列值转换为行，并将另一组列值作为新表的列，以便更直观地反映数据之间的关系。

例如，下表是销售数据记录表：

可以使用交叉表将销售额按品类和地区做一个汇总：

注意到交叉表不仅将原来的行数据转换为了列数据，还对这些列数据进行了汇总统计。

2. 使用MSSQL制作交叉表

2.1 简单交叉表

在MSSQL中，可以使用PIVOT关键字快速制作交叉表。以下是一个使用PIVOT制作简单交叉表的示例：

-- 创建示例数据表

CREATE TABLE Sales (
  SaleID INT PRIMARY KEY,
  Category VARCHAR(50),
  Region VARCHAR(50),
  Amount INT
);
-- 插入示例数据
INSERT INTO Sales VALUES (1, '家电', '北京', 3500);
INSERT INTO Sales VALUES (2, '数码', '上海', 4800);
INSERT INTO Sales VALUES (3, '服装', '北京', 2800);
INSERT INTO Sales VALUES (4, '家电', '上海', 5800);
INSERT INTO Sales VALUES (5, '数码', '北京', 3900);
INSERT INTO Sales VALUES (6, '服装', '上海', 3200);
-- 制作简单交叉表
SELECT Category, [北京], [上海]
FROM (
  SELECT Category, Region, Amount
  FROM Sales
) AS s
PIVOT (
  SUM(Amount)
  FOR Region IN ([北京], [上海])
) AS p;

以上语句将销售数据表Sales中的数据按照品类和地区制作了一个简单交叉表，交叉表的结果如下：

2.2 带有总计的交叉表

为了更完整地反映数据之间的关系，有时需要在交叉表中加入各种汇总信息，例如总计、小计等。以下是一个带有总计的交叉表示例：

-- 带有总计的交叉表

SELECT Category, [北京], [上海], [总计]
FROM (
  SELECT Category, Region, Amount
  FROM Sales
  UNION ALL
  SELECT Category, '总计', SUM(Amount)
  FROM Sales
  GROUP BY Category
) AS s
PIVOT (
  SUM(Amount)
  FOR Region IN ([北京], [上海], [总计])
) AS p
ORDER BY Category;

以上语句生成的交叉表如下：

可以看到，以上交叉表在每个品类和每个地区的数据之外，还增加了一行总计。

2.3 带有列汇总数据的交叉表

为了更全面地反映数据之间的关系，有时候需要在交叉表中添加列汇总信息。以下是一个带有列汇总数据的交叉表示例：

-- 带有列汇总数据的交叉表

SELECT Category, [北京], [上海], [总计], [平均值]
FROM (
  SELECT Category, Region, Amount
  FROM Sales
  UNION ALL
  SELECT '汇总', Region, SUM(Amount)
  FROM Sales
  GROUP BY Region
) AS s
PIVOT (
  SUM(Amount)
  FOR Region IN ([北京], [上海], [总计])
) AS p
CROSS APPLY (
  SELECT AVG(Amount) AS [平均值]
  FROM Sales
) AS a
ORDER BY Category;

以上语句生成的交叉表如下：

可以看到，以上交叉表在每列数据之外，还增加了一列平均值数据。

3. 总结

交叉表是一种将行数据转换为列数据的技术，用于对数据进行分类汇总和展示。在MSSQL中，可以使用PIVOT关键字来制作交叉表。交叉表可以增加各种汇总信息，例如总计、小计、列汇总数据等，从而更完整地反映数据之间的关系。