如何使用Pivot/UnPivot实现酒店房间表动态行转列按楼层输出房间号

固定列实现方案（已知楼层为Floor 001~Floor 005时使用） #

SELECT 
    [Floor 001], [Floor 002], [Floor 003], [Floor 004], [Floor 005]
FROM (
    SELECT 
        RoomNumber,
        RoomFloorTitle,
        -- 同楼层内按房间ID排序生成序号，作为转置后的行标识
        ROW_NUMBER() OVER (PARTITION BY RoomFloorTitle ORDER BY RoomID) AS row_seq
    FROM [HotelOnline].[dbo].[XtblRooms]
) AS source_data
PIVOT (
    -- 每个行序号+楼层组合仅对应1个房间，用MAX/MIN均可正确取值
    MAX(RoomNumber)
    FOR RoomFloorTitle IN ([Floor 001], [Floor 002], [Floor 003], [Floor 004], [Floor 005])
) AS pivot_result
ORDER BY row_seq

动态列实现方案（自动适配楼层数量，无需手动修改列名） #

DECLARE @column_list NVARCHAR(MAX), @dynamic_sql NVARCHAR(MAX)

-- 拼接所有去重后的楼层列名
SELECT @column_list = STRING_AGG(QUOTENAME(RoomFloorTitle), ',') 
FROM (SELECT DISTINCT RoomFloorTitle FROM [HotelOnline].[dbo].[XtblRooms]) AS distinct_floors

-- 拼接完整转置查询语句
SET @dynamic_sql = N'
SELECT ' + @column_list + N'
FROM (
    SELECT 
        RoomNumber,
        RoomFloorTitle,
        ROW_NUMBER() OVER (PARTITION BY RoomFloorTitle ORDER BY RoomID) AS row_seq
    FROM [HotelOnline].[dbo].[XtblRooms]
) AS source_data
PIVOT (
    MAX(RoomNumber)
    FOR RoomFloorTitle IN (' + @column_list + N')
) AS pivot_result
ORDER BY row_seq
'

-- 执行动态SQL
EXEC sp_executesql @dynamic_sql

低版本SQL Server兼容处理 #

如果你的SQL Server版本低于2017，不支持STRING_AGG函数，可将上述动态列方案中拼接@column_list的部分替换为以下写法：

SELECT @column_list = STUFF((
    SELECT DISTINCT ',' + QUOTENAME(RoomFloorTitle)
    FROM [HotelOnline].[dbo].[XtblRooms]
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

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