SQL Server中如何对含Tunnel1、Tunnel2等列的表进行透视关联？

当然可以实现，无需修改原表结构，通过UNION ALL或UNPIVOT结合表关联就能满足需求，完全兼容现有VBA程序的依赖。以下是具体实现方式：

方法一：使用UNION ALL（兼容性强，适配所有SQL Server版本） #

假设遗留产品表名为Products，结构包含ProductName以及对应4个隧道的LbsHr、LbsStroke字段（例如Tunnel1_LbsHr、Tunnel1_LbsStroke……Tunnel4_LbsHr、Tunnel4_LbsStroke）；规范的Tunnels表包含TunnelId（对应产品表的隧道序号）和TunnelName字段。

执行以下SQL语句：

SELECT 
    t.TunnelName,
    p.ProductName,
    p.LbsHr,
    p.LbsStroke
FROM (
    -- 将产品表的4组隧道数据拆分为行
    SELECT ProductName, Tunnel1_LbsHr AS LbsHr, Tunnel1_LbsStroke AS LbsStroke, 1 AS TunnelId
    FROM Products
    WHERE ProductName = 'Product 1' -- 若需处理所有产品，可删除此条件
    UNION ALL
    SELECT ProductName, Tunnel2_LbsHr AS LbsHr, Tunnel2_LbsStroke AS LbsStroke, 2 AS TunnelId
    FROM Products
    WHERE ProductName = 'Product 1'
    UNION ALL
    SELECT ProductName, Tunnel3_LbsHr AS LbsHr, Tunnel3_LbsStroke AS LbsStroke, 3 AS TunnelId
    FROM Products
    WHERE ProductName = 'Product 1'
    UNION ALL
    SELECT ProductName, Tunnel4_LbsHr AS LbsHr, Tunnel4_LbsStroke AS LbsStroke, 4 AS TunnelId
    FROM Products
    WHERE ProductName = 'Product 1'
) p
JOIN Tunnels t ON p.TunnelId = t.TunnelId
-- 可选：过滤无有效数据的行
WHERE p.LbsHr IS NOT NULL AND p.LbsStroke IS NOT NULL

方法二：使用UNPIVOT（代码更简洁，适配SQL Server 2005及以上版本） #

如果产品表的隧道字段命名规则统一，可通过UNPIVOT简化代码：

SELECT 
    t.TunnelName,
    p.ProductName,
    p.LbsHr,
    p.LbsStroke
FROM (
    SELECT 
        ProductName,
        CAST(SUBSTRING(TunnelCol, 7, 1) AS INT) AS TunnelId,
        LbsHr,
        LbsStroke
    FROM (
        SELECT 
            ProductName,
            Tunnel1_LbsHr, Tunnel2_LbsHr, Tunnel3_LbsHr, Tunnel4_LbsHr,
            Tunnel1_LbsStroke, Tunnel2_LbsStroke, Tunnel3_LbsStroke, Tunnel4_LbsStroke
        FROM Products
        WHERE ProductName = 'Product 1'
    ) src
    UNPIVOT (
        LbsHr FOR TunnelCol IN (Tunnel1_LbsHr, Tunnel2_LbsHr, Tunnel3_LbsHr, Tunnel4_LbsHr)
    ) upvt_hr
    UNPIVOT (
        LbsStroke FOR TunnelColStroke IN (Tunnel1_LbsStroke, Tunnel2_LbsStroke, Tunnel3_LbsStroke, Tunnel4_LbsStroke)
    ) upvt_stroke
    -- 确保同一条隧道的两组数据匹配
    WHERE SUBSTRING(TunnelCol, 7, 1) = SUBSTRING(TunnelColStroke, 7, 1)
) p
JOIN Tunnels t ON p.TunnelId = t.TunnelId
WHERE p.LbsHr IS NOT NULL AND p.LbsStroke IS NOT NULL

补充说明 #

• 两种方法都不会改动原表结构，完全不影响现有VBA程序的运行。
• 若产品表的隧道字段命名有差异，只需调整SQL中对应的字段名称即可。
• 如需对所有产品生效，删除WHERE子句中的ProductName = 'Product 1'条件即可。

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