我明白你遇到的问题了：动态生成年份透视列后，没有对应年份数据的行返回NULL，需要把这些NULL替换成0。咱们来一步步修改你的SQL代码，解决这个问题。

核心思路 #

动态透视后出现NULL是因为某些分组在对应年份没有数据，我们需要对每个透视列使用ISNULL(列名, 0)来将NULL替换为0。同时，为了确保逻辑清晰，我们要避免使用SELECT *，而是明确列出所有需要的列。

修改后的完整代码 #

DECLARE @query AS NVARCHAR(MAX), @cols AS NVARCHAR(MAX), @cols_with_isnull AS NVARCHAR(MAX), @anyoUltPax AS NVARCHAR(MAX), @anyoAntPax AS NVARCHAR(MAX);

-- 生成原始年份列（用于PIVOT子句）
SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME([Año]) FROM paxESTOP WHERE ([Año] >= YEAR(GETDATE())-7) AND ([Año] <= YEAR(GETDATE())-1) FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'');

-- 生成带ISNULL处理的年份列（用于SELECT子句，自动把NULL转0）
SET @cols_with_isnull = STUFF((SELECT DISTINCT ', ISNULL(' + QUOTENAME([Año]) + ', 0) AS ' + QUOTENAME([Año]) FROM paxESTOP WHERE ([Año] >= YEAR(GETDATE())-7) AND ([Año] <= YEAR(GETDATE())-1) FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'');

SET @anyoUltPax = CONVERT(INT, CASE WHEN IsNumeric(CONVERT(VARCHAR(4),YEAR(GETDATE())-1)) = 1 THEN CONVERT(VARCHAR(4),YEAR(GETDATE())-1) ELSE 0 END)
SET @anyoAntPax = CONVERT(INT, CASE WHEN IsNumeric(CONVERT(VARCHAR(4),YEAR(GETDATE())-2)) = 1 THEN CONVERT(VARCHAR(4),YEAR(GETDATE())-2) ELSE 0 END)

-- 重构SELECT语句：明确列出分组列 + 处理后的年份列，替换原来的SELECT *
set @query = 'SELECT 
    Origen, Destino, [Compañía], País, Zona, [CCAA base], [CCAA dest], [Ciudad Destino], Distancia,
    ' + @cols_with_isnull + ',
    [' + @anyoUltPax + ']-[' + @anyoAntPax+ '] as [Diferencia anual] 
FROM ( 
    SELECT 
        [apto_base] as Origen, 
        [apto_dest] as Destino, 
        [cia_id] as [Compañía], 
        [dim_Paises].pais AS País, 
        [dim_Zonas].zona AS Zona, 
        [CCAA_org].CCAA AS [CCAA base], 
        [CCAA_dest].CCAA AS [CCAA dest], 
        [ciudad] AS [Ciudad Destino], 
        Distancia, 
        Año, 
        Pax 
    FROM (
        SELECT 
            [apto_base] ,[apto_dest] ,[cia_id] ,[pais_id] ,[zona_id] ,[CCAA_id_org] ,[CCAA_id_dest] ,[distancia] ,[año] as [Año] ,SUM([pax_tot]) as Pax 
        FROM [MKTPBI].[dbo].[paxESTOP] 
        WHERE ([Año] >= YEAR(GETDATE())-7) AND ([Año] <= YEAR(GETDATE())-1) 
        GROUP BY [apto_base],[apto_dest],[cia_id],[pais_id],[zona_id],[CCAA_id_org],[CCAA_id_dest],[distancia],[Año] 
    ) pax 
    LEFT JOIN dim_Paises ON pax.pais_id = dim_Paises.pais_id 
    LEFT JOIN dim_Zonas ON pax.zona_id = dim_Zonas.zona_id 
    LEFT JOIN dim_CCAA as CCAA_org ON pax.CCAA_id_org = CCAA_org.CCAA_id 
    LEFT JOIN dim_CCAA as CCAA_dest ON pax.CCAA_id_dest = CCAA_dest.CCAA_id 
    LEFT JOIN dim_Aeropuertos ON pax.apto_dest = dim_Aeropuertos.apto_id 
) x 
pivot ( 
    sum(x.Pax) for [Año] in (' + @cols + ') 
) p 
order by ['+@anyoUltPax+'] desc '

execute(@query)

关键改动说明 #

1. 新增@cols_with_isnull变量：
   这个变量会自动把每个年份列转换成ISNULL([年份], 0) AS [年份]的格式，这样查询返回时，所有NULL值都会被替换为0。
2. 替换SELECT *为明确列：
   直接用SELECT *会包含未处理的NULL列，我们改为明确列出所有分组列（Origen、Destino等）加上处理后的年份列，确保每一列都符合预期。
3. 年度差计算自动生效：
   因为年份列已经被处理为0（而非NULL），所以[Diferencia anual]的计算不会出现NULL - 数值的错误，结果会正确显示为0或实际差值。

可选进阶：强制显示所有目标年份 #

如果某些年份在paxESTOP中完全没有数据，当前代码不会生成对应列。如果需要强制显示最近7年的所有年份（哪怕没有数据），可以用CTE生成完整年份列表，再和原始数据做交叉连接，确保每个分组都有所有年份的记录：

-- 在内部pax子查询前添加这段CTE生成完整年份列表
WITH YearsCTE AS (
    SELECT YEAR(GETDATE())-7 AS Año
    UNION ALL
    SELECT Año + 1 FROM YearsCTE WHERE Año < YEAR(GETDATE())-1
)

-- 替换原来的pax子查询为：
SELECT 
    g.[apto_base], g.[apto_dest], g.[cia_id], g.[pais_id], g.[zona_id], g.[CCAA_id_org], g.[CCAA_id_dest], g.[distancia], y.Año,
    ISNULL(SUM(p.[pax_tot]), 0) AS Pax
FROM (
    SELECT DISTINCT [apto_base],[apto_dest],[cia_id],[pais_id],[zona_id],[CCAA_id_org],[CCAA_id_dest],[distancia]
    FROM [MKTPBI].[dbo].[paxESTOP] 
    WHERE ([Año] >= YEAR(GETDATE())-7) AND ([Año] <= YEAR(GETDATE())-1)
) g
CROSS JOIN YearsCTE y
LEFT JOIN [MKTPBI].[dbo].[paxESTOP] p 
    ON g.[apto_base] = p.[apto_base] 
    AND g.[apto_dest] = p.[apto_dest]
    AND g.[cia_id] = p.[cia_id]
    AND g.[pais_id] = p.[pais_id]
    AND g.[zona_id] = p.[zona_id]
    AND g.[CCAA_id_org] = p.[CCAA_id_org]
    AND g.[CCAA_id_dest] = p.[CCAA_id_dest]
    AND g.[distancia] = p.[distancia]
    AND p.[Año] = y.Año
GROUP BY g.[apto_base],g.[apto_dest],g.[cia_id],g.[pais_id],g.[zona_id],g.[CCAA_id_org],g.[CCAA_id_dest],g.[distancia], y.Año

这样就能确保透视结果中包含所有7年的列，没有数据的年份显示为0。

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