SQL技巧 数据转换 老生常谈：行转列与列转行操作解析

🔥 SQL技巧 | 数据转换 | 老生常谈：行转列与列转行操作解析

📢 最新动态
2025年7月，随着数据分析需求激增，SQL的行列转换技术依然是面试高频考点，据最新统计，85%的数据分析师在ETL（数据提取、转换、加载）过程中仍需频繁处理行列转换问题，今天我们就来彻底搞懂这个"老熟人"！

为什么需要行列转换？🤔

数据存储和展示的需求往往不同：

• 行转列（Pivot）：适合将多行数据汇总为一行，便于横向对比（如销售报表按月展示）
• 列转行（Unpivot）：适合将宽表变长表，方便聚合计算（如将多个月份字段转为单列）

举个栗子🌰：
原始销售数据可能是每天一行，但老板想看"每个产品在各月的总销量"，这时候就需要行转列！

行转列实战（Pivot）💡

标准SQL方案（CASE WHEN）

SELECT 
    product_id,
    SUM(CASE WHEN month = '2025-01' THEN sales ELSE 0 END) AS jan_sales,
    SUM(CASE WHEN month = '2025-02' THEN sales ELSE 0 END) AS feb_sales
FROM sales_data
GROUP BY product_id;

数据库特有函数

• MySQL 8.0+: 直接使用PIVOT语法（需CTE配合）
• SQL Server: 原生支持PIVOT关键字

  -- SQL Server示例
  SELECT * FROM (
    SELECT product_id, month, sales 
    FROM sales_data
  ) AS src
  PIVOT (
    SUM(sales) FOR month IN ([2025-01], [2025-02])
  ) AS pvt;

动态生成列（高级技巧）✨

当列不固定时,可能需要动态SQL（存储过程实现）：

-- PostgreSQL示例
EXECUTE (
    SELECT 'SELECT product_id, ' || 
    string_agg('SUM(CASE WHEN month = ''' || month || ''' THEN sales END) AS ' || month || '_sales', ', ')
    || ' FROM sales_data GROUP BY product_id'
    FROM (SELECT DISTINCT month FROM sales_data) t
);

列转行实战（Unpivot）🔄

标准SQL方案（UNION ALL）

SELECT product_id, '2025-01' AS month, jan_sales AS sales FROM sales_pivoted
UNION ALL
SELECT product_id, '2025-02' AS month, feb_sales AS sales FROM sales_pivoted;

数据库特有函数

• SQL Server: UNPIVOT关键字

  SELECT product_id, month, sales
  FROM sales_pivoted
  UNPIVOT (
    sales FOR month IN (jan_sales, feb_sales)
  ) AS unpvt;

• PostgreSQL: CROSS JOIN LATERAL + jsonb

  

  SELECT product_id, kv.key AS month, kv.value::int AS sales
  FROM sales_pivoted
  CROSS JOIN LATERAL jsonb_each_text(
    jsonb_build_object(
        '2025-01', jan_sales,
        '2025-02', feb_sales
    )
  ) AS kv;

避坑指南🚧

1. 性能陷阱：行列转换会显著增加计算量，大数据量时考虑物化视图
2. NULL值处理：记得用COALESCE(sales, 0)避免空值干扰
3. 动态列陷阱：生产环境慎用动态SQL，可能有注入风险
4. 跨数据库兼容：PIVOT/UNPIVOT语法在不同DBMS中差异较大

现代SQL新趋势🌟

2025年部分云数据库已支持更简洁语法：

• Snowflake：PIVOT支持动态列名自动检测
• BigQuery：UNPIVOT INCLUDE NULLS可保留空值记录

🎯 总结
行列转换就像数据界的"变形金刚"，掌握后能让数据随心所欲变换形态！

• 行转列用CASE WHEN或PIVOT
• 列转行用UNION ALL或UNPIVOT
• 永远先确认转换后的数据是否符合分析需求

下次面试官再问这个问题,你可以自信地说："这题我熟！" 💪