Replace Null with ‘-‘ string in Dynamic Pivot query

I have the following code where I’m trying to dynamically Pivot the table.

 SELECT DISTINCT        lton.order_number           AS OrderNo,
                        c.compound_name             AS compoundName, 
                        lt.batch_number             AS batch_number,
                        CASE WHEN lt.machine_number = 0 THEN m.name
                             ELSE ltn.test_machine
                        END AS test_machine,
                        CASE WHEN lt.pass_customer_specs = 1 THEN 'Pass'
                            ELSE 'Fail'
                        END AS passCustomer

INTO #TEMP2
FROM   lab_test lt
JOIN lab_test_compound ltc 
    ON ltc.id = lt.lab_test_compound_id
JOIN compound c 
    ON c.id = ltc.compound_id
LEFT JOIN lab_test_machine_configuration ltmc
    ON ltmc.id = ltc.lab_test_machine_configuration_id
LEFT JOIN lab_test_machine ltn
    ON ltn.id = ltmc.lab_test_machine_id
LEFT JOIN lab_test_measurement ltm 
    ON ltm.lab_test_machine_id = ltn.id
LEFT JOIN measurement m
    ON m.id = ltm.measurement_id
LEFT JOIN lab_test_order_number lton
    ON lton.lab_test_id = lt.id
WHERE lt.date_created >= '2020-01-30 06:55:42.997' 
    AND lt.last_test = 1;

SELECT @Columns1 = COALESCE(@Columns1 + ', ','') + QUOTENAME(test_machine)
FROM
    ( SELECT DISTINCT test_machine
      FROM   #TEMP2) AS B
ORDER BY B.test_machine

SET @columns3 = '   SELECT  compoundName, 
                            OrderNo, 
                            batch_number, 
                            '+ ISNULL(@Columns1,'-') +'
                    FROM   ( SELECT * 
                             FROM   #TEMP2) AS PivotData
                    PIVOT (MIN(passcustomer)
                        FOR test_machine IN ('+ @Columns1 +')) AS pvt1 
                    ORDER  BY compoundName, batch_number'

EXEC(@columns3)
DROP TABLE #TEMP2

Everything seems to be working the way I want except I want to replace the null with ‘-‘.

Any ideas?

Answer

You need to put the coalesce into the dynamic column list – something like this:

SELECT @Columns1 = 'COALESCE(' + QUOTENAME(test_machine) + ',''-''),' +  @Columns1 
FROM
    ( SELECT DISTINCT test_machine
  FROM   #TEMP2) AS B
ORDER BY B.test_machine;

SELECT @Columns1 = SUBSTRING(@Columns1,1,LEN(@Columns1)-1);

The isnull(@Columns1… you have will replace null column names with ‘-‘ rather than null column contents.

Advertisement

Answer