• Of course to further take advantage of the above we could add a totalling function:

    SELECT SalesPerson, COALESCE([Oranges],0) AS Oranges,

    COALESCE([Pickles],0) AS Pickles,

    COALESCE([Bananas],0) AS Bananas,

    COALESCE([Apples],0) AS Apples,

    COALESCE([Grapes],0) AS Grapes,

    COALESCE([Melons],0) AS Melons,

    COALESCE([Beans],0) AS Beans,

    COALESCE([Oranges],0) +

    COALESCE([Pickles],0) +

    COALESCE([Bananas],0) +

    COALESCE([Apples],0) +

    COALESCE([Grapes],0) +

    COALESCE([Melons],0) +

    COALESCE([Beans],0) AS Total

    FROM

    (SELECT SalesPerson, Product, SalesAmount

    FROM names) ps

    PIVOT (SUM(SalesAmount)

    FOR Product IN ([Oranges], [Pickles], [Bananas], [Apples], [Grapes], [Melons], [Beans])

    ) AS pvt