如何在UNPIVOT查询中包含NULL值？求方案及示例

原查询使用UNPIVOT操作时会自动过滤值为NULL的行，但业务场景需要保留这些NULL记录。下面对比两种替代方案，并给出适配你现有查询的示例。

推荐方案：CROSS APPLY #

CROSS APPLY搭配值列表的方式更简洁，可读性和可维护性更强，尤其是处理多列时。它可直接将多列转换为行，同时保留NULL值，还能避免两次UNPIVOT导致的笛卡尔积问题（原查询两次UNPIVOT会生成20*20=400行，而CROSS APPLY仅生成20行，与原始列数匹配）。

修改后的完整查询 #

SELECT 
    PARTNO, DATETIME, PLANT_NAME, SAP_PLANT_CODE, SHIFT, EVENT, BASE_CODE, SIGNON_ID, 
    PROPERTY, LINE_NUMBER, SSIZE, NCU, SUMDEFECTS, PARTNOAUX, PROCESSAUX, DATETIMEAUX, 
    UDL7, MACHINE, DMS_SAP_SPEC_NO, UDL10, UDL11, CAVITY, ROW_NUMBER, DEPARTMENT, UDL15, 
    UDL16, UDL17, INSPECTOR, SAP_MATERIAL_NUMBER, UDL20, POSITION, LOCATION, UDL23, 
    HOT_COLD, UDL25, UDL26, SPEC_TYPE, PROPERTY_TREE, UDL29, DEFECT_TYPE, UDL31, UDL32, 
    MOLDBASE, UDL34, UDL35, UDL36, UDL37, CUSTOMER, UDL39, SAMPLEDATE, UDL41, UDL42, 
    UDL43, UDL44, UDL45, UDL46, SUB_INSPECTION, PC_COLLECT_2,
    ca.DefectName, ca.DefectValue, ca.CountName, ca.CountValue
FROM (
    SELECT 
        OC_DDATA_PC.PARTNO, OC_DDATA_PC.DATETIME, stagingPLM.dbo.PLANTS.PLANT_NAME, 
        OC_DDATA_PC.UDL1 AS SAP_PLANT_CODE, OC_DDATA_PC.UDL2 AS SHIFT, OC_DDATA_PC.EVENT, 
        OC_DDATA_PC.UDL3 AS BASE_CODE, OC_DDATA_PC.UDL4 AS SIGNON_ID, OC_DDATA_PC.UDL5 AS PROPERTY, 
        OC_DDATA_PC.UDL6 AS LINE_NUMBER, OC_DDATA_PC.SSIZE, OC_DDATA_PC.NCU, OC_DDATA_PC.SUMDEFECTS, 
        OC_DDAT_AUX_PC.PARTNOAUX, OC_DDAT_AUX_PC.PROCESSAUX, OC_DDAT_AUX_PC.DATETIMEAUX, 
        OC_DDAT_AUX_PC.UDL7, OC_DDAT_AUX_PC.UDL8 AS MACHINE, OC_DDAT_AUX_PC.UDL9 AS DMS_SAP_SPEC_NO, 
        OC_DDAT_AUX_PC.UDL10, OC_DDAT_AUX_PC.UDL11, OC_DDAT_AUX_PC.UDL12 AS CAVITY, 
        OC_DDAT_AUX_PC.UDL13 AS ROW_NUMBER, OC_DDAT_AUX_PC.UDL14 AS DEPARTMENT, OC_DDAT_AUX_PC.UDL15, 
        OC_DDAT_AUX_PC.UDL16, OC_DDAT_AUX_PC.UDL17, OC_DDAT_AUX_PC.UDL18 AS INSPECTOR, 
        OC_DDAT_AUX_PC.UDL19 AS SAP_MATERIAL_NUMBER, OC_DDAT_AUX_PC.UDL20, OC_DDAT_AUX_PC.UDL21 AS POSITION, 
        OC_DDAT_AUX_PC.UDL22 AS LOCATION, OC_DDAT_AUX_PC.UDL23, OC_DDAT_AUX_PC.UDL24 AS HOT_COLD, 
        OC_DDAT_AUX_PC.UDL25, OC_DDAT_AUX_PC.UDL26, OC_DDAT_AUX_PC.UDL27 AS SPEC_TYPE, 
        OC_DDAT_AUX_PC.UDL28 AS PROPERTY_TREE, OC_DDAT_AUX_PC.UDL29, OC_DDAT_AUX_PC.UDL30 AS DEFECT_TYPE, 
        OC_DDAT_AUX_PC.UDL31, OC_DDAT_AUX_PC.UDL32, OC_DDAT_AUX_PC.UDL33 AS MOLDBASE, 
        OC_DDAT_AUX_PC.UDL34, OC_DDAT_AUX_PC.UDL35, OC_DDAT_AUX_PC.UDL36, OC_DDAT_AUX_PC.UDL37, 
        OC_DDAT_AUX_PC.UDL38 AS CUSTOMER, OC_DDAT_AUX_PC.UDL39, OC_DDAT_AUX_PC.UDL40 AS SAMPLEDATE, 
        OC_DDAT_AUX_PC.UDL41, OC_DDAT_AUX_PC.UDL42, OC_DDAT_AUX_PC.UDL43, OC_DDAT_AUX_PC.UDL44, 
        OC_DDAT_AUX_PC.UDL45, OC_DDAT_AUX_PC.UDL46, OC_DDAT_AUX_PC.UDL47 AS SUB_INSPECTION, 
        OC_DDAT_AUX_PC.UDL48 AS PC_COLLECT_2,
        OC_DMDL_PC.DESCRIPT AS DefectName1, OC_DMDL_PC_1.DESCRIPT AS DefectName2, 
        OC_DMDL_PC_2.DESCRIPT AS DefectName3, OC_DMDL_PC_3.DESCRIPT AS DefectName4, 
        OC_DMDL_PC_4.DESCRIPT AS DefectName5, OC_DMDL_PC_5.DESCRIPT AS DefectName6, 
        OC_DMDL_PC_6.DESCRIPT AS DefectName7, OC_DMDL_PC_7.DESCRIPT AS DefectName8, 
        OC_DMDL_PC_8.DESCRIPT AS DefectName9, OC_DMDL_PC_9.DESCRIPT AS DefectName10, 
        OC_DMDL_PC_10.DESCRIPT AS DefectName11, OC_DMDL_PC_11.DESCRIPT AS DefectName12, 
        OC_DMDL_PC_12.DESCRIPT AS DefectName13, OC_DMDL_PC_13.DESCRIPT AS DefectName14, 
        OC_DMDL_PC_14.DESCRIPT AS DefectName15, OC_DMDL_PC_15.DESCRIPT AS DefectName16, 
        OC_DMDL_PC_16.DESCRIPT AS DefectName17, OC_DMDL_PC_17.DESCRIPT AS DefectName18, 
        OC_DMDL_PC_18.DESCRIPT AS DefectName19, OC_DMDL_PC_19.DESCRIPT AS DefectName20,
        OC_DDATA_PC.DEFECTCNT1, OC_DDATA_PC.DEFECTCNT2, OC_DDATA_PC.DEFECTCNT3, 
        OC_DDATA_PC.DEFECTCNT4, OC_DDATA_PC.DEFECTCNT5, OC_DDATA_PC.DEFECTCNT6, 
        OC_DDATA_PC.DEFECTCNT7, OC_DDATA_PC.DEFECTCNT8, OC_DDATA_PC.DEFECTCNT9, 
        OC_DDATA_PC.DEFECTCNT10, OC_DDATA_PC.DEFECTCNT11, OC_DDATA_PC.DEFECTCNT12, 
        OC_DDATA_PC.DEFECTCNT13, OC_DDATA_PC.DEFECTCNT14, OC_DDATA_PC.DEFECTCNT15, 
        OC_DDATA_PC.DEFECTCNT16, OC_DDATA_PC.DEFECTCNT17, OC_DDATA_PC.DEFECTCNT18, 
        OC_DDATA_PC.DEFECTCNT19, OC_DDATA_PC.DEFECTCNT20
    FROM OC_DDATA_PC 
    INNER JOIN OC_DDAT_AUX_PC 
        ON OC_DDATA_PC.PARTNO = OC_DDAT_AUX_PC.PARTNOAUX 
        AND OC_DDATA_PC.DATETIME = OC_DDAT_AUX_PC.DATETIMEAUX 
    INNER JOIN stagingPLM.dbo.PLANTS 
        ON OC_DDATA_PC.UDL1 = stagingPLM.dbo.PLANTS.PLANT_CODE 
    LEFT OUTER JOIN OC_DMDL_PC 
        ON OC_DDATA_PC.DEFECTID1 = OC_DMDL_PC.DEFECTID 
    LEFT OUTER JOIN OC_DMDL_PC AS OC_DMDL_PC_1 
        ON OC_DDATA_PC.DEFECTID2 = OC_DMDL_PC_1.DEFECTID 
    LEFT OUTER JOIN OC_DMDL_PC AS OC_DMDL_PC_2 
        ON OC_DDATA_PC.DEFECTID3 = OC_DMDL_PC_2.DEFECTID 
    LEFT OUTER JOIN OC_DMDL_PC AS OC_DMDL_PC_3 
        ON OC_DDATA_PC.DEFECTID4 = OC_DMDL_PC_3.DEFECTID 
    LEFT OUTER JOIN OC_DMDL_PC AS OC_DMDL_PC_4 
        ON OC_DDATA_PC.DEFECTID5 = OC_DMDL_PC_4.DEFECTID 
    LEFT OUTER JOIN OC_DMDL_PC AS OC_DMDL_PC_5 
        ON OC_DDATA_PC.DEFECTID6 = OC_DMDL_PC_5.DEFECTID 
    LEFT OUTER JOIN OC_DMDL_PC AS OC_DMDL_PC_6 
        ON OC_DDATA_PC.DEFECTID7 = OC_DMDL_PC_6.DEFECTID 
    LEFT OUTER JOIN OC_DMDL_PC AS OC_DMDL_PC_7 
        ON OC_DDATA_PC.DEFECTID8 = OC_DMDL_PC_7.DEFECTID 
    LEFT OUTER JOIN OC_DMDL_PC AS OC_DMDL_PC_8 
        ON OC_DDATA_PC.DEFECTID9 = OC_DMDL_PC_8.DEFECTID 
    LEFT OUTER JOIN OC_DMDL_PC AS OC_DMDL_PC_9 
        ON OC_DDATA_PC.DEFECTID10 = OC_DMDL_PC_9.DEFECTID 
    LEFT OUTER JOIN OC_DMDL_PC AS OC_DMDL_PC_10 
        ON OC_DDATA_PC.DEFECTID11 = OC_DMDL_PC_10.DEFECTID 
    LEFT OUTER JOIN OC_DMDL_PC AS OC_DMDL_PC_11 
        ON OC_DDATA_PC.DEFECTID12 = OC_DMDL_PC_11.DEFECTID 
    LEFT OUTER JOIN OC_DMDL_PC AS OC_DMDL_PC_12 
        ON OC_DDATA_PC.DEFECTID13 = OC_DMDL_PC_12.DEFECTID 
    LEFT OUTER JOIN OC_DMDL_PC AS OC_DMDL_PC_13 
        ON OC_DDATA_PC.DEFECTID14 = OC_DMDL_PC_13.DEFECTID 
    LEFT OUTER JOIN OC_DMDL_PC AS OC_DMDL_PC_14 
        ON OC_DDATA_PC.DEFECTID15 = OC_DMDL_PC_14.DEFECTID 
    LEFT OUTER JOIN OC_DMDL_PC AS OC_DMDL_PC_15 
        ON OC_DDATA_PC.DEFECTID16 = OC_DMDL_PC_15.DEFECTID 
    LEFT OUTER JOIN OC_DMDL_PC AS OC_DMDL_PC_16 
        ON OC_DDATA_PC.DEFECTID17 = OC_DMDL_PC_16.DEFECTID 
    LEFT OUTER JOIN OC_DMDL_PC AS OC_DMDL_PC_17 
        ON OC_DDATA_PC.DEFECTID18 = OC_DMDL_PC_17.DEFECTID 
    LEFT OUTER JOIN OC_DMDL_PC AS OC_DMDL_PC_18 
        ON OC_DDATA_PC.DEFECTID19 = OC_DMDL_PC_18.DEFECTID 
    LEFT OUTER JOIN OC_DMDL_PC AS OC_DMDL_PC_19 
        ON OC_DDATA_PC.DEFECTID20 = OC_DMDL_PC_19.DEFECTID
) AS SourceData
CROSS APPLY (
    VALUES
        ('DefectName1', DefectName1, 'DEFECTCNT1', DEFECTCNT1),
        ('DefectName2', DefectName2, 'DEFECTCNT2', DEFECTCNT2),
        ('DefectName3', DefectName3, 'DEFECTCNT3', DEFECTCNT3),
        ('DefectName4', DefectName4, 'DEFECTCNT4', DEFECTCNT4),
        ('DefectName5', DefectName5, 'DEFECTCNT5', DEFECTCNT5),
        ('DefectName6', DefectName6, 'DEFECTCNT6', DEFECTCNT6),
        ('DefectName7', DefectName7, 'DEFECTCNT7', DEFECTCNT7),
        ('DefectName8', DefectName8, 'DEFECTCNT8', DEFECTCNT8),
        ('DefectName9', DefectName9, 'DEFECTCNT9', DEFECTCNT9),
        ('DefectName10', DefectName10, 'DEFECTCNT10', DEFECTCNT10),
        ('DefectName11', DefectName11, 'DEFECTCNT11', DEFECTCNT11),
        ('DefectName12', DefectName12, 'DEFECTCNT12', DEFECTCNT12),
        ('DefectName13', DefectName13, 'DEFECTCNT13', DEFECTCNT13),
        ('DefectName14', DefectName14, 'DEFECTCNT14', DEFECTCNT14),
        ('DefectName15', DefectName15, 'DEFECTCNT15', DEFECTCNT15),
        ('DefectName16', DefectName16, 'DEFECTCNT16', DEFECTCNT16),
        ('DefectName17', DefectName17, 'DEFECTCNT17', DEFECTCNT17),
        ('DefectName18', DefectName18, 'DEFECTCNT18', DEFECTCNT18),
        ('DefectName19', DefectName19, 'DEFECTCNT19', DEFECTCNT19),
        ('DefectName20', DefectName20, 'DEFECTCNT20', DEFECTCNT20)
) AS ca(DefectName, DefectValue, CountName, CountValue)

备选方案：CROSS JOIN + CASE #

这种方案通过生成数字序列，再用CASE语句匹配对应列，也能保留NULL值，但写法繁琐，适合列数较少的场景。

示例代码 #

SELECT 
    PARTNO, DATETIME, PLANT_NAME, SAP_PLANT_CODE, SHIFT, EVENT, BASE_CODE, SIGNON_ID, 
    PROPERTY, LINE_NUMBER, SSIZE, NCU, SUMDEFECTS, PARTNOAUX, PROCESSAUX, DATETIMEAUX, 
    UDL7, MACHINE, DMS_SAP_SPEC_NO, UDL10, UDL11, CAVITY, ROW_NUMBER, DEPARTMENT, UDL15, 
    UDL16, UDL17, INSPECTOR, SAP_MATERIAL_NUMBER, UDL20, POSITION, LOCATION, UDL23, 
    HOT_COLD, UDL25, UDL26, SPEC_TYPE, PROPERTY_TREE, UDL29, DEFECT_TYPE, UDL31, UDL32, 
    MOLDBASE, UDL34, UDL35, UDL36, UDL37, CUSTOMER, UDL39, SAMPLEDATE, UDL41, UDL42, 
    UDL43, UDL44, UDL45, UDL46, SUB_INSPECTION, PC_COLLECT_2,
    CASE n.Num
        WHEN 1 THEN 'DefectName1'
        WHEN 2 THEN 'DefectName2'
        WHEN 3 THEN 'DefectName3'
        WHEN 4 THEN 'DefectName4'
        WHEN 5 THEN 'DefectName5'
        WHEN 6 THEN 'DefectName6'
        WHEN 7 THEN 'Defect