SQL Server用unpivot/cross apply处理空值实现州立公园预约表规范化

测试数据准备 #

-- 示例stage阶段表定义&测试数据
CREATE TABLE #StageParkData (
    TransDescription VARCHAR(50),
    Date1 DATE,
    Date2 DATE,
    Date3 DATE,
    Date4 DATE,
    Date5 DATE,
    LPlate1 VARCHAR(20),
    LPlate2 VARCHAR(20)
);
INSERT INTO #StageParkData
(TransDescription, Date1, Date2, Date3, Date4, Date5, LPlate1, LPlate2)
VALUES
('DailyEntry x3', '2021-10-10', '2021-10-11', '2021-10-12', NULL, NULL, 'AB12345', NULL),
('Annual Sticker Reg', NULL, NULL, NULL, NULL, NULL, 'CD45678', NULL),
('Annual 2 for $55', NULL, NULL, NULL, NULL, NULL, 'XY85245', 'TR12345'),
('Annual 1 for $35', NULL, NULL, NULL, NULL, NULL, 'UYH5545', NULL);

核心转换SQL #

SELECT 
    s.TransDescription,
    p.PassType,
    v.VisitDate,
    l.LicensePlate
FROM #StageParkData s
-- 计算PassType
CROSS APPLY (
    SELECT CASE 
        WHEN COALESCE(s.Date1, s.Date2, s.Date3, s.Date4, s.Date5) IS NULL 
        THEN 'Season' ELSE 'Daily' END AS PassType
) p
-- 展开日期列
CROSS APPLY (
    SELECT VisitDate = NULL WHERE p.PassType = 'Season'
    UNION ALL
    SELECT Date1 WHERE p.PassType = 'Daily' AND Date1 IS NOT NULL
    UNION ALL
    SELECT Date2 WHERE p.PassType = 'Daily' AND Date2 IS NOT NULL
    UNION ALL
    SELECT Date3 WHERE p.PassType = 'Daily' AND Date3 IS NOT NULL
    UNION ALL
    SELECT Date4 WHERE p.PassType = 'Daily' AND Date4 IS NOT NULL
    UNION ALL
    SELECT Date5 WHERE p.PassType = 'Daily' AND Date5 IS NOT NULL
) v
-- 展开车牌列
CROSS APPLY (
    SELECT LPlate1 AS LicensePlate WHERE LPlate1 IS NOT NULL
    UNION ALL
    SELECT LPlate2 AS LicensePlate WHERE LPlate2 IS NOT NULL
) l

逻辑说明 #

• 用CROSS APPLY实现行列转换，比UNPIVOT语法更灵活，无需额外写空值过滤逻辑
• PassType计算通过COALESCE批量判断5个日期列是否全为空，符合规则要求
• 日期列自动适配场景：日卡仅返回非空的有效日期，季卡统一返回NULL作为VisitDate
• 车牌列自动适配1个/2个车牌的场景，仅返回非空的车牌值
• 两个展开逻辑的笛卡尔乘积完全匹配需求：日卡的每个日期对应每个关联车牌，季卡的NULL日期对应每个关联车牌，输出结果和要求的目标结构完全一致

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