SQL Server中的透视表(PIVOT)应用

不过说实话，刚开始用的时候也踩过不少坑。比如聚合函数的选择，还有列名的动态变化，都得特别小心。

SQL Server的PIVOT，其实Excel的数据透视表也是异曲同工。虽然一个是写代码，一个是拖拽操作，但思路都差不多，重点无外乎行转列，还是列转行。

基础数据准备

首先，让我们创建示例数据表：

-- 创建销售数据表
CREATETABLE SalesData (
    Region NVARCHAR(50),
    QuarterNVARCHAR(2),
    Sales DECIMAL(10,2)
);

-- 插入示例数据
INSERTINTO SalesData (Region, Quarter, Sales) VALUES
    ('North', 'Q1', 10000),
    ('North', 'Q2', 12000),
    ('North', 'Q3', 15000),
    ('North', 'Q4', 11000),
    ('South', 'Q1', 8000),
    ('South', 'Q2', 9000),
    ('South', 'Q3', 11000),
    ('South', 'Q4', 10000),
    ('East', 'Q1', 9000),
    ('East', 'Q2', 10000),
    ('East', 'Q3', 12000),
    ('East', 'Q4', 11500),
    ('West', 'Q1', 7000),
    ('West', 'Q2', 8500),
    ('West', 'Q3', 9500),
    ('West', 'Q4', 9000);

复制

使用PIVOT进行数据转换

基本透视表查询

SELECT *
FROM (
    SELECT Region, Quarter, Sales
    FROM SalesData
) AS SourceTable
PIVOT (
    SUM(Sales)
    FOR Quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS PivotTable;

复制

这个查询将产生如下结果：

添加计算列的透视表

SELECT 
    Region,
    [Q1], [Q2], [Q3], [Q4],
    ([Q1] + [Q2] + [Q3] + [Q4]) as YearTotal,
    ([Q1] + [Q2] + [Q3] + [Q4])/4as YearAverage
FROM (
    SELECT Region, Quarter, Sales
    FROM SalesData
) AS SourceTable
PIVOT (
    SUM(Sales)
    FORQuarterIN ([Q1], [Q2], [Q3], [Q4])
) AS PivotTable;

复制

带有百分比的透视表

WITH PivotedData AS (
    SELECT *
    FROM (
        SELECT Region, Quarter, Sales
        FROM SalesData
    ) AS SourceTable
    PIVOT (
        SUM(Sales)
        FORQuarterIN ([Q1], [Q2], [Q3], [Q4])
    ) AS PivotTable
)
SELECT
    Region,
    [Q1],
    [Q2],
    [Q3],
    [Q4],
    CAST([Q1] * 100.0 / ([Q1] + [Q2] + [Q3] + [Q4]) ASDECIMAL(5,2)) as Q1_Percentage,
    CAST([Q2] * 100.0 / ([Q1] + [Q2] + [Q3] + [Q4]) ASDECIMAL(5,2)) as Q2_Percentage,
    CAST([Q3] * 100.0 / ([Q1] + [Q2] + [Q3] + [Q4]) ASDECIMAL(5,2)) as Q3_Percentage,
    CAST([Q4] * 100.0 / ([Q1] + [Q2] + [Q3] + [Q4]) ASDECIMAL(5,2)) as Q4_Percentage
FROM PivotedData;

复制

动态PIVOT查询

当列值不固定时，可以使用动态SQL：

DECLARE @columnsNVARCHAR(MAX) = '';
DECLARE @sqlNVARCHAR(MAX) = '';

-- 获取所有季度并构建列名
SELECT @columns = STRING_AGG(QUOTENAME(Quarter), ',')
FROM (SELECTDISTINCTQuarterFROM SalesData) AS Quarters;

-- 构建动态SQL
SET @sql = N'
SELECT *
FROM (
    SELECT Region, Quarter, Sales
    FROM SalesData
) AS SourceTable
PIVOT (
    SUM(Sales)
    FOR Quarter IN (' + @columns + ')
) AS PivotTable;
';

-- 执行动态SQL
EXEC sp_executesql @sql;

复制

注意事项

• PIVOT操作可能会影响查询性能，特别是在大数据集上
• 动态PIVOT查询需要适当的权限才能执行
• 确保聚合函数（如SUM）适合您的数据类型
• 在使用百分比计算时注意除零错误

这篇文章详细介绍了SQL Server中透视表的基本概念和实际应用，从简单的数据转换到复杂的动态查询都有覆盖。通过这些示例，您可以更好地理解和使用PIVOT功能来进行数据分析。

如果你正在从事上位机、自动化、机器视觉、物联网（IOT）项目或数字化转型方面的工作，欢迎加入我的微信圈子！在这里，我们不仅可以轻松畅聊最新技术动态和行业趋势，还能够在技术问题上互相帮助和支持。我会尽量利用我的知识和经验来帮助你解决问题，当然也期待从大家的专业见解中学习和成长。无论你是新手还是老鸟，期待与志同道合的朋友交流心得，一起进步！