Would something like this work?
SELECT
a.[Item#],
SUM(QtySold) AS [Sale Total],
SUM( CASE WHEN b.[Store#] = 'Store A' THEN b.QtySold ELSE 0 END) AS [Store A],
SUM( CASE WHEN b.[Store#] = 'Store B' THEN b.QtySold ELSE 0 END) AS [Store B]
--- etc
FROM
ItemMaster a
LEFT JOIN ItemMovement b ON
a.[Item#] = b.[Item#]
GROUP BY GROUPING SETS
( (a.[Item#]),());
No comments:
Post a Comment