SELECT
    storename,
    sum(christmas_party_ns) AS christmas_party_ns,
    sum(christmas_party_tc) AS christmas_party_tc,
    sum(delivery_ns) AS delivery_ns,
    sum(delivery_tc) AS delivery_tc,
    sum(kiddie_party_ns) AS kiddie_party_ns,
    sum(kiddie_party_tc) AS kiddie_party_tc,
    sum(dine_in_ns) AS dine_in_ns,
    sum(dine_in_tc) AS dine_in_tc,
    sum(roving_vendor_ns) AS roving_vendor_ns,
    sum(roving_vendor_tc) AS roving_vendor_tc,
    sum(take_out_ns) AS take_out_ns,
    sum(take_out_tc) AS take_out_tc
FROM
(
    SELECT
        CASE WHEN transactiontype = 'Christmas Party'
        THEN ns ELSE 0
        END AS christmas_party_ns,
        CASE WHEN transactiontype = 'Christmas Party'
        THEN tc ELSE 0
        END AS christmas_party_tc,

        CASE WHEN transactiontype = 'Delivery'
        THEN ns ELSE 0
        END AS delivery_ns,
        CASE WHEN transactiontype = 'Delivery'
        THEN tc ELSE 0
        END AS delivery_tc,

        CASE WHEN transactiontype = 'Kiddie Party'
        THEN ns ELSE 0
        END AS kiddie_party_ns,
        CASE WHEN transactiontype = 'Kiddie Party'
        THEN tc ELSE 0
        END AS kiddie_party_tc,

        CASE WHEN transactiontype = 'Dine-In'
        THEN ns ELSE 0
        END AS dine_in_ns,
        CASE WHEN transactiontype = 'Dine-In'
        THEN tc ELSE 0
        END AS dine_in_tc,

        CASE WHEN transactiontype = 'Roving Vendor'
        THEN ns ELSE 0
        END AS roving_vendor_ns,
        CASE WHEN transactiontype = 'Roving Vendor'
        THEN tc ELSE 0
        END AS roving_vendor_tc,

        CASE WHEN transactiontype = 'Take-out'
        THEN ns ELSE 0
        END AS take_out_ns,
        CASE WHEN transactiontype = 'Take-out'
        THEN tc ELSE 0
        END AS take_out_tc,

        CASE WHEN transactiontype = 'Crew Meal'
        THEN ns ELSE 0
        END AS crew_meal_ns,
        CASE WHEN transactiontype = 'Crew Meal'
        THEN tc ELSE 0
        END AS crew_meal_tc,

        CASE WHEN transactiontype = 'LO Sold'
        THEN ns ELSE 0
        END AS lo_sold_ns,
        CASE WHEN transactiontype = 'LO Sold'
        THEN tc ELSE 0
        END AS lo_sold_tc,

        CASE WHEN transactiontype = 'Managers Meal'
        THEN ns ELSE 0
        END AS managers_meal_ns,
        CASE WHEN transactiontype = 'Managers Meal'
        THEN tc ELSE 0
        END AS managers_meal_tc,

        CASE WHEN transactiontype = 'Mania DR'
        THEN ns ELSE 0
        END AS mania_dr_ns,
        CASE WHEN transactiontype = 'Mania DR'
        THEN tc ELSE 0
        END AS mania_dr_tc,

        CASE WHEN transactiontype = 'Prod Shortage'
        THEN ns ELSE 0
        END AS prod_shortage_ns,
        CASE WHEN transactiontype = 'Prod Shortage'
        THEN tc ELSE 0
        END AS prod_shortage_tc,

        CASE WHEN transactiontype = 'Product Test'
        THEN ns ELSE 0
        END AS product_test_ns,
        CASE WHEN transactiontype = 'Product Test'
        THEN tc ELSE 0
        END AS product_test_tc,

        CASE WHEN transactiontype = 'Spoilage'
        THEN ns ELSE 0
        END AS spoilage_ns,
        CASE WHEN transactiontype = 'Spoilage'
        THEN tc ELSE 0
        END AS spoilage_tc,

        CASE WHEN transactiontype = 'Booth Selling'
        THEN ns ELSE 0
        END AS booth_selling_ns,
        CASE WHEN transactiontype = 'Booth Selling'
        THEN tc ELSE 0
        END AS booth_selling_tc,

        CASE WHEN transactiontype = 'Bulk Order'
        THEN ns ELSE 0
        END AS bulk_order_ns,
        CASE WHEN transactiontype = 'Bulk Order'
        THEN tc ELSE 0
        END AS bulk_order_tc,

        CASE WHEN transactiontype = 'RDS-4'
        THEN ns ELSE 0
        END AS rds_4_ns,
        CASE WHEN transactiontype = 'RDS-4'
        THEN tc ELSE 0
        END AS rds_4_tc,

        storename
    FROM
    (
        SELECT
            storename, transactiontype, sum(subtotal) as ns, sum(txncount) as tc
        FROM
            agg.storedate_summary
        WHERE
            transactiontype IN ('Christmas Party', 'Delivery', 'Dine-In', 'Kiddie Party', 'Roving Vendor', 'Take-out') AND
            dt BETWEEN '2007-01-01' AND '2007-01-02'
        GROUP BY storename, transactiontype
        ORDER BY storename, transactiontype
    ) AS x
) AS y
GROUP BY storename