如何对vwLocations视图执行动态Pivot行转列获取单人多地址列数据

实现逻辑 #

• 首先使用ROW_NUMBER()窗口函数对同个人员的地址进行编号，序号对应最终列名中的地址序号
• 用UNPIVOT将单个地址的5个属性字段拆为多行，同时拼接生成目标列名
• 动态生成所有需要的列名列表，最后执行动态Pivot完成行转列

完整代码 #

DECLARE @cols AS NVARCHAR(MAX), @sql AS NVARCHAR(MAX)

-- 动态生成所有地址序号对应的列名
SELECT @cols = STUFF((
    SELECT ',' + QUOTENAME('Addr' + CAST(AddrSeq AS VARCHAR) + 'Address1')
           + ',' + QUOTENAME('Addr' + CAST(AddrSeq AS VARCHAR) + 'Address2')
           + ',' + QUOTENAME('Addr' + CAST(AddrSeq AS VARCHAR) + 'City')
           + ',' + QUOTENAME('Addr' + CAST(AddrSeq AS VARCHAR) + 'State')
           + ',' + QUOTENAME('Addr' + CAST(AddrSeq AS VARCHAR) + 'Zip')
    FROM (
        SELECT DISTINCT ROW_NUMBER() OVER(PARTITION BY PersonID ORDER BY Address1) AS AddrSeq
        FROM vwLocations
    ) AS SeqList
    ORDER BY AddrSeq
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

-- 拼接执行的动态SQL语句
SET @sql = N'
SELECT PersonID, ' + @cols + N'
FROM (
    SELECT 
        PersonID,
        ColName = ''Addr'' + CAST(AddrSeq AS VARCHAR) + ColName,
        ColValue
    FROM (
        -- 对同人员地址编号
        SELECT 
            PersonID,
            ROW_NUMBER() OVER(PARTITION BY PersonID ORDER BY Address1) AS AddrSeq,
            Address1, Address2, City, State, Zip
        FROM vwLocations
    ) AS BaseData
    UNPIVOT (
        ColValue FOR ColName IN (Address1, Address2, City, State, Zip)
    ) AS UnpivData
) AS SrcData
PIVOT (
    MAX(ColValue)
    FOR ColName IN (' + @cols + N')
) AS PivResult
ORDER BY PersonID
'

-- 执行查询
EXEC sp_executesql @sql

注意事项 #

• 地址序号的排序规则可自行修改ROW_NUMBER()中的ORDER BY子句，比如按地址录入时间排序
• 若使用MySQL、PostgreSQL等其他数据库，仅需调整对应数据库的行转列、动态SQL语法，核心逻辑保持一致

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