SQL Server 2012中如何实现UNPIVOT多列转换及零值过滤？

这个场景我太熟悉了——要把成对的列转换为行，还要保证对应关系，同时过滤无效行。在SQL Server 2012里，**CROSS APPLY结合VALUES**是处理这类多列关联unpivot的最优方案，比单独用UNPIVOT再关联要简洁得多，还不容易出错。

核心思路 #

我们需要把每一组对应的列（比如CGL和CGLTria、CPL和CPLTria、EO和EOTria）映射为一行数据，同时保留Policy Number、Policy Effective Date等基础列，最后过滤掉Premium为0的行。

具体实现代码 #

假设你的源表名为PolicyData，包含以下列：PolicyNumber, PolicyEffectiveDate, CGL, CPL, EO, CGLTria, CPLTria, EOTria。对应的SQL语句如下：

SELECT
    pd.PolicyNumber,
    pd.PolicyEffectiveDate,
    ca.CoverageType,
    ca.Premium,
    ca.TriaPremium
FROM PolicyData pd
CROSS APPLY (
    -- 这里把每一组列映射为一行，确保CoverageType和对应的值一一对应
    VALUES
        ('CGL', pd.CGL, pd.CGLTria),
        ('CPL', pd.CPL, pd.CPLTria),
        ('EO', pd.EO, pd.EOTria)
) ca (CoverageType, Premium, TriaPremium)
-- 过滤掉Premium为0的行
WHERE ca.Premium <> 0;

代码解释 #

1. CROSS APPLY + VALUES：这部分相当于把源表的每一行拆成3行（对应3种Coverage Type），同时把每组的Premium和Tria Premium值绑定在一起，完美保证了对应关系，不会出现错位。
2. 保留原表列：直接从源表pd中选择PolicyNumber、PolicyEffectiveDate等需要保留的列即可，不需要额外处理。
3. 过滤条件：直接在WHERE子句中过滤Premium <> 0的行，简单直接。

对比传统UNPIVOT方法（不推荐） #

如果用传统的UNPIVOT分别处理两列，再通过关联合并，代码会复杂很多，还容易出错：

WITH PremiumCTE AS (
    SELECT
        PolicyNumber,
        PolicyEffectiveDate,
        CoverageType,
        Premium
    FROM PolicyData
    UNPIVOT (
        Premium FOR CoverageType IN (CGL, CPL, EO)
    ) up
),
TriaPremiumCTE AS (
    SELECT
        PolicyNumber,
        PolicyEffectiveDate,
        -- 这里需要手动处理列名后缀，容易出错
        CoverageType = LEFT(CoverageType, LEN(CoverageType) - 4),
        TriaPremium
    FROM PolicyData
    UNPIVOT (
        TriaPremium FOR CoverageType IN (CGLTria, CPLTria, EOTria)
    ) up
)
SELECT
    p.PolicyNumber,
    p.PolicyEffectiveDate,
    p.CoverageType,
    p.Premium,
    t.TriaPremium
FROM PremiumCTE p
JOIN TriaPremiumCTE t
    ON p.PolicyNumber = t.PolicyNumber
    AND p.PolicyEffectiveDate = t.PolicyEffectiveDate
    AND p.CoverageType = t.CoverageType
WHERE p.Premium <> 0;

这种方法不仅需要手动处理列名的后缀（比如去掉Tria），还需要两次UNPIVOT再关联，性能和可读性都不如CROSS APPLY的方案。

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