• tod.novak (4/10/2014)


    mickyT,

    This worked perfectly! Thank you so much for the help. I'm going to look up COALESCE to help me understand how it worked.

    Thank you everyone for the replies. I appreciate all the quick responses!

    COALESCE is used to return the first non null value from the list of parameters. ISNULL could also have been used in this situation, but as todays question of the day demonstrated be careful with datatypes.

    This sort of shows what the different options do

    WITH testData AS (

    SELECT *

    FROM (VALUES

    (CAST('Grp A' AS VARCHAR(5)), 2, 1),

    (CAST('Grp A' AS VARCHAR(5)), 2, 2),

    (CAST('Grp A' AS VARCHAR(5)), 2, 3),

    (CAST('Grp A' AS VARCHAR(5)), 2, 4),

    (CAST('Grp B' AS VARCHAR(5)), 4, 1),

    (CAST('Grp B' AS VARCHAR(5)), 4, 2),

    (CAST('Grp B' AS VARCHAR(5)), 4, 3),

    (CAST('Grp B' AS VARCHAR(5)), 4, 4)

    ) AS TD(Name, Value, Seq)

    )

    SELECT Name UntouchedName,

    COALESCE(Name, 'Grand Total') CoalescedName,

    ISNULL(Name, 'Grand Total') IsNullName,

    SUM(Value) TotalValue

    FROM testData

    GROUP BY ROLLUP (Name);

    UntouchedName CoalescedName IsNullName TotalValue

    ------------- ------------- ---------- -----------

    Grp A Grp A Grp A 8

    Grp B Grp B Grp B 16

    NULL Grand Total Grand 24