SQL Server生成城市-位置矩阵表的优化方案咨询

在SQL Server中，**动态PIVOT（行转列）**是替代游标+动态加列的成熟高效方案，基于集合操作实现，性能远优于游标逐行迭代的方式。以下是具体实现步骤和代码：

核心思路 #

通过动态获取所有位置名称作为列名，利用PIVOT运算符将位置行转列为矩阵的列，再通过判断城市与位置的关联关系填充X。

完整代码示例 #

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX)

-- 1. 动态获取所有位置名称，拼接成PIVOT需要的列格式
SELECT @cols = STRING_AGG(QUOTENAME(Location), ', ')
FROM tLocation

-- 2. 构建动态PIVOT查询语句
SET @query = N'
SELECT City, ' + @cols + '
FROM (
    -- 先关联三张表，生成城市-位置的基础数据集，存在关联则标记''X''
    SELECT 
        c.City,
        l.Location,
        CASE WHEN cl.CityId IS NOT NULL THEN ''X'' ELSE '''' END AS LocationFlag
    FROM tCity c
    CROSS JOIN tLocation l  -- 笛卡尔积生成所有城市-位置组合
    LEFT JOIN tCityLocation cl 
        ON c.Id = cl.CityId AND l.Id = cl.LocationId
) AS SourceData
PIVOT (
    MAX(LocationFlag)  -- 聚合函数取标记值（因为每个城市-位置组合唯一，MAX/MIN都可以）
    FOR Location IN (' + @cols + ')
) AS PivotTable
ORDER BY City'

-- 3. 执行动态SQL
EXEC sp_executesql @query

代码说明 #

• STRING_AGG：SQL Server 2017及以上版本可用，用来拼接所有位置名称为带引号的列名格式（如[北京], [上海]）；如果是低版本，可改用FOR XML PATH的方式拼接。
• CROSS JOIN：生成所有城市和位置的组合，确保矩阵中不会遗漏任何行或列。
• LEFT JOIN tCityLocation：判断城市与位置是否存在关联，存在则标记X，否则为空。
• PIVOT：将位置行转列为列，通过聚合函数提取对应标记值。

优势对比 #

• 性能：基于集合操作，避免游标逐行迭代的开销，大数据量下性能提升明显。
• 代码简洁：逻辑清晰，无需复杂的游标循环和动态列添加逻辑。
• 可维护性：后续位置或城市新增时，代码无需修改，自动适配新数据。

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