SQL Server如何将属于同一SHIP_ID的所有值合并到同一行显示

实现逻辑 #

原始数据按Unique_key升序排列后，每个业务分组结构固定为：SHIP_ID非空行 → SHIP_COUNTRY非空行 → 若干DISTANCE_CODE非空行。我们只需要把前面出现的非空SHIP_ID、SHIP_COUNTRY向下填充到后续空值行，再过滤掉DISTANCE_CODE为空的行即可得到预期结果。

兼容全版本SQL Server的查询代码 #

WITH fill_cte AS (
    SELECT 
        DISTANCE_CODE,
        -- 向下填充非空SHIP_ID
        MAX(SHIP_ID) OVER (ORDER BY Unique_key ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SHIP_ID,
        -- 向下填充非空SHIP_COUNTRY
        MAX(SHIP_COUNTRY) OVER (ORDER BY Unique_key ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SHIP_COUNTRY
    FROM 你的原始表名
)
SELECT DISTANCE_CODE, SHIP_ID, SHIP_COUNTRY
FROM fill_cte
-- 过滤掉仅存SHIP_ID/SHIP_COUNTRY的无效行，空值同时兼容NULL和空字符串场景
WHERE DISTANCE_CODE IS NOT NULL AND DISTANCE_CODE != ''
ORDER BY SHIP_ID;

SQL Server 2022+ 简化写法 #

2022及以上版本支持IGNORE NULLS参数，可以直接取最近的非空值，写法更直观：

WITH fill_cte AS (
    SELECT 
        DISTANCE_CODE,
        LAST_VALUE(SHIP_ID) IGNORE NULLS OVER (ORDER BY Unique_key) AS SHIP_ID,
        LAST_VALUE(SHIP_COUNTRY) IGNORE NULLS OVER (ORDER BY Unique_key) AS SHIP_COUNTRY
    FROM 你的原始表名
)
SELECT DISTANCE_CODE, SHIP_ID, SHIP_COUNTRY
FROM fill_cte
WHERE DISTANCE_CODE IS NOT NULL AND DISTANCE_CODE != ''
ORDER BY SHIP_ID;

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