hi
hope this helps
create tables
-- Markets table
CREATE TABLE GH_ExportMarkets (
ID INT PRIMARY KEY,
Name VARCHAR(50)
);
-- Seasons table
CREATE TABLE GH_ExportSeasons (
ID INT PRIMARY KEY,
ProductGroupID INT,
SeasonName VARCHAR(50)
);
-- Market stats table
CREATE TABLE GH_MarketStats (
ID INT PRIMARY KEY,
SeasonID INT,
MarketID INT,
CropID VARCHAR(20),
WeekID INT,
WeightByAir INT,
WeightBySea INT
);
insert data
-- Markets
INSERT INTO GH_ExportMarkets VALUES
(1, 'USA'),
(2, 'UK'),
(3, 'Germany');
-- Seasons
INSERT INTO GH_ExportSeasons VALUES
(1, 100, 'Summer'),
(2, 100, 'Winter');
-- Market stats
INSERT INTO GH_MarketStats VALUES
(1, 1, 1, 'Apple', 1, 10, 20), -- USA Week 1
(2, 1, 1, 'Apple', 2, 15, 25), -- USA Week 2
(3, 1, 2, 'Apple', 1, 5, 10), -- UK Week 1
(4, 1, 2, 'Apple', 2, 8, 12), -- UK Week 2
(5, 1, 3, 'Apple', 1, 7, 13), -- Germany Week 1
(6, 1, 3, 'Apple', 2, 9, 11); -- Germany Week 2
DECLARE @Cols NVARCHAR(MAX), @RowTotal NVARCHAR(MAX), @SQL NVARCHAR(MAX);
-- Build week columns + row total expression
SELECT @Cols = STRING_AGG(QUOTENAME(WeekID), ','),
@RowTotal = STRING_AGG('ISNULL(' + QUOTENAME(WeekID) + ',0)', '+')
FROM (SELECT DISTINCT WeekID FROM GH_MarketStats) W;
-- Dynamic SQL
SET @SQL = '
;WITH P AS (
SELECT EM.Name, MS.WeekID, MS.WeightByAir + MS.WeightBySea AS Tot
FROM GH_MarketStats MS
JOIN GH_ExportSeasons ES ON ES.ID = MS.SeasonID
JOIN GH_ExportMarkets EM ON EM.ID = MS.MarketID
WHERE ES.ProductGroupID = 100 AND MS.SeasonID = 1 AND MS.CropID = ''Apple''
)
, Pivoted AS (
SELECT Name, ' + @Cols + '
FROM P
PIVOT (SUM(Tot) FOR WeekID IN (' + @Cols + ')) pv
)
SELECT Name, ' + @Cols + ', ' + @RowTotal + ' AS RowTotal
FROM Pivoted
UNION ALL
SELECT ''Grand Total'', ' + @Cols + ', ' + @RowTotal + '
FROM (
SELECT ''All'' AS Name, WeekID, SUM(Tot) AS Tot
FROM P
GROUP BY WeekID
) AS Agg
PIVOT (SUM(Tot) FOR WeekID IN (' + @Cols + ')) pv;
';
EXEC sp_executesql @SQL;
