在TSQL中拆分逗号分隔字符串并将结果转换为行（含区间展开）

输入表：

需将Rooms列中的单个房间号、房间号范围展开，生成每行对应一个房间号的输出表：

解决方案 #

1. Excel/Power Query实现 #

1. 选中数据区域，进入数据选项卡，点击从表格/区域导入Power Query编辑器。
2. 选中Rooms列，点击转换→拆分列→按分隔符，选择逗号作为分隔符，拆分成行。
3. 新增自定义列，输入以下公式处理房间范围：

   = if Text.Contains([Rooms], "-") then List.Numbers(Number.From(Text.BeforeDelimiter([Rooms], "-")), Number.From(Text.AfterDelimiter([Rooms], "-")) - Number.From(Text.BeforeDelimiter([Rooms], "-")) + 1) else {Number.From([Rooms])}
   

4. 展开自定义列的列表值，删除原Rooms列，将新列重命名为Rooms，最后关闭并上载数据。

2. Python（Pandas）实现 #

import pandas as pd

def expand_rooms(room_str):
    rooms = []
    parts = room_str.split(',')
    for part in parts:
        if '-' in part:
            start, end = map(int, part.split('-'))
            rooms.extend(range(start, end+1))
        else:
            rooms.append(int(part))
    return rooms

# 构造输入数据
df = pd.DataFrame({
    'ID': [1],
    'BuildingName': ['BurjKhalifa'],
    'Rooms': ['2,4-9,12'],
    'Details': ['RoomsAvailable'],
    'Contact': ['+971-12-0000']
})

# 展开Rooms列
df['Rooms'] = df['Rooms'].apply(expand_rooms)
df = df.explode('Rooms').reset_index(drop=True)

print(df)

运行代码后即可得到目标输出表。

3. SQL（MySQL）实现 #

假设数据存储在building_rooms表中，通过递归CTE实现：

WITH RECURSIVE room_expander AS (
    SELECT 
        ID,
        BuildingName,
        SUBSTRING_INDEX(Rooms, ',', 1) AS room_part,
        SUBSTRING(Rooms FROM LENGTH(SUBSTRING_INDEX(Rooms, ',', 1)) + 2) AS remaining_rooms,
        Details,
        Contact
    FROM building_rooms
    WHERE Rooms != ''
    
    UNION ALL
    
    SELECT 
        ID,
        BuildingName,
        SUBSTRING_INDEX(remaining_rooms, ',', 1) AS room_part,
        SUBSTRING(remaining_rooms FROM LENGTH(SUBSTRING_INDEX(remaining_rooms, ',', 1)) + 2) AS remaining_rooms,
        Details,
        Contact
    FROM room_expander
    WHERE remaining_rooms != ''
),
range_expander AS (
    SELECT
        ID,
        BuildingName,
        IF(LOCATE('-', room_part) > 0, SUBSTRING_INDEX(room_part, '-', 1), room_part) AS start_room,
        IF(LOCATE('-', room_part) > 0, SUBSTRING_INDEX(room_part, '-', -1), room_part) AS end_room,
        Details,
        Contact
    FROM room_expander
),
final_rooms AS (
    SELECT
        ID,
        BuildingName,
        start_room + n - 1 AS Rooms,
        Details,
        Contact
    FROM range_expander
    JOIN (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) AS nums
    ON n <= end_room - start_room + 1
)
SELECT * FROM final_rooms ORDER BY Rooms;

注：若房间号范围超过10，需扩展nums表中的数值数量。

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