Oracle SQL UNPIVOT保留不需要的空行问题求助

问题根源 #

UNPIVOT 默认仅排除NULL值，若你的数据中空值是空字符串''，或者原行中仅单个Tool列为空，UNPIVOT会生成不需要的空行。同时你需要保留原行中两个Tool列都为空的记录，因此需要额外的过滤逻辑。

解决方案1：UNPIVOT + 条件补全 #

先通过UNPIVOT过滤掉非空的Tool行，再单独补回完全无工具关联的记录：

WITH UnpivotedValid AS (
    SELECT DISTINCT Part, Chart, Tool, "T Link"
    FROM your_table
    UNPIVOT(
        Tool for "T Link" in ("Tool Part" as Part, "Tool Chart" as Chart)
    ) u
    -- 过滤空值和空字符串，根据实际数据调整
    WHERE Tool IS NOT NULL AND Tool <> ''
)
-- 合并有效行与完全无工具的行
SELECT Part, Chart, Tool, "T Link" FROM UnpivotedValid
UNION ALL
SELECT DISTINCT Part, Chart, NULL AS Tool, NULL AS "T Link"
FROM your_table
WHERE ("Tool Part" IS NULL OR "Tool Part" = '')
  AND ("Tool Chart" IS NULL OR "Tool Chart" = '');

解决方案2：UNION ALL手动转置（更灵活） #

直接用UNION ALL拆分列，精准控制每个分支的过滤条件，避免生成无效空行：

-- 保留Tool Part非空的行
SELECT DISTINCT
    Part,
    Chart,
    "Tool Part" AS Tool,
    'Part' AS "T Link"
FROM your_table
WHERE "Tool Part" IS NOT NULL AND "Tool Part" <> ''

UNION ALL

-- 保留Tool Chart非空的行
SELECT DISTINCT
    Part,
    Chart,
    "Tool Chart" AS Tool,
    'Chart' AS "T Link"
FROM your_table
WHERE "Tool Chart" IS NOT NULL AND "Tool Chart" <> ''

UNION ALL

-- 保留完全无工具关联的行
SELECT DISTINCT
    Part,
    Chart,
    NULL AS Tool,
    NULL AS "T Link"
FROM your_table
WHERE ("Tool Part" IS NULL OR "Tool Part" = '')
  AND ("Tool Chart" IS NULL OR "Tool Chart" = '');

注意事项 #

• 替换your_table为你的实际表名；
• 若数据中空值仅为NULL，可去掉判断空字符串的条件（如Tool <> ''）；
• DISTINCT可根据原数据是否有重复行决定是否保留。

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