Pivot Tables in SQL

Transact-SQL
1

Hi all

I’m new to Pivot tables in SQL, below is my query but I’m battling to get the Grand totals at the end of each line, hope someaone can help me

Thanks

DECLARE @Cols VARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);

         SELECT @Cols = COALESCE (@Cols + ',[' + convert(varchar,MS.WeekID) + ']', '[' + convert(varchar,MS.WeekID) + ']') 
                      FROM GH_MarketStats MS
                           inner join GH_ExportSeasons ES on ES.ID=MS.SeasonID
                           inner join GH_ExportMarkets EM on EM.ID=MS.MarketID
           where  ES.ProductGroupID = @ProductGroupID
           GROUP BY MS.WeekID
           ORDER  BY MS.WeekID;

SELECT @SQL = CONCAT('SELECT * FROM (',
        'SELECT EM.[Name], MS.WeekID, (MS.WeightbyAir + MS.WeightbySea) as Tot',
        ' FROM GH_MarketStats MS',
        ' INNER JOIN GH_ExportSeasons ES on ES.ID = MS.SeasonID',
        ' INNER JOIN GH_ExportMarkets EM on EM.ID = MS.MarketID',
        ' WHERE ES.ProductGroupID = ''', @ProductGroupID, '''',
        ' AND MS.SeasonID = ', @SeasonID,
		' AND MS.CropID = ''', @CropID, '''',
  ') AS SourceTable',
  ' PIVOT (',
        ' SUM(Tot)',
        ' FOR WeekID IN (',@cols, ')',
 ' ) AS PivotTable'
);

   EXEC SP_EXECUTESQL @SQL

END

Currently this is what I have showing using above query :

Name 22 23 24 25 26

Canada NULL NULL NULL NULL 2607
China NULL NULL 359 NULL NULL
Europe NULL NULL NULL 2100 NULL
Hong Kong NULL 1250 NULL NULL 1152
India NULL NULL NULL NULL NULL
Taiwan 8472 6585 NULL NULL NULL
Thailand NULL NULL 5250 4989 NULL
2

Can you supply some DDL and sample data so we can help?

3

Hi Mike

Thanks for looking at my SP problem, I figured it out

4

For future readers who land on this thread with the same “row total on a dynamic PIVOT” problem: one simple pattern is to keep the PIVOT just for the week columns, and calculate the grand total in a separate grouped subquery, then join it back in. That way you don’t have to build a dynamic “[22] + [23] + ...” expression. In your case the dynamic part stays almost exactly as you wrote it, and inside the dynamic SQL you can do something like:

SELECT @SQL = CONCAT(
'WITH Base AS (
SELECT EM.[Name],
MS.WeekID,
Tot = MS.WeightbyAir + MS.WeightbySea
FROM GH_MarketStats MS
INNER JOIN GH_ExportSeasons ES ON ES.ID = MS.SeasonID
INNER JOIN GH_ExportMarkets EM ON EM.ID = MS.MarketID
WHERE ES.ProductGroupID = ''', @ProductGroupID, '''
AND MS.SeasonID = ', @SeasonID, '
AND MS.CropID = ''', @CropID, '''
),
Totals AS (
SELECT [Name], GrandTotal = SUM(Tot)
FROM Base
GROUP BY [Name]
),
Pivoted AS (
SELECT *
FROM Base
PIVOT (
SUM(Tot) FOR WeekID IN (', @Cols, ')
) AS P
)
SELECT p.*, t.GrandTotal
FROM Pivoted p
JOIN Totals t ON t.[Name] = p.[Name];'
);

That gives you one row per market (Name), the week columns from the PIVOT, and a GrandTotal column at the end, without needing any extra dynamic string building for the total itself.

5

hi

hope this helps

DDL
Sample Data

- GH_ExportSeasons: contains seasons with their product group
CREATE TABLE GH_ExportSeasons (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    ProductGroupID VARCHAR(50) NOT NULL
);

-- GH_ExportMarkets: market definitions
CREATE TABLE GH_ExportMarkets (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(100) NOT NULL
);

-- GH_MarketStats: weekly stats per market/season/crop
CREATE TABLE GH_MarketStats (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    WeekID INT NOT NULL,
    SeasonID INT NOT NULL,
    MarketID INT NOT NULL,
    CropID VARCHAR(50) NOT NULL,
    WeightbyAir DECIMAL(18,6) NOT NULL,
    WeightbySea DECIMAL(18,6) NOT NULL,
    CONSTRAINT FK_MarketStats_Season FOREIGN KEY (SeasonID) REFERENCES GH_ExportSeasons(ID),
    CONSTRAINT FK_MarketStats_Market FOREIGN KEY (MarketID) REFERENCES GH_ExportMarkets(ID),
    CONSTRAINT UQ_MarketStats UNIQUE (SeasonID, MarketID, WeekID, CropID)
);

-- Optional index to help pivot queries
CREATE INDEX IX_MarketStats_SeasonWeek ON GH_MarketStats(SeasonID, WeekID);

-- 1) Seed seasons (ID will be 1, 2 ...)
SET IDENTITY_INSERT GH_ExportSeasons ON;
INSERT INTO GH_ExportSeasons (ID, ProductGroupID) VALUES
    (1, 'PG-A'),
    (2, 'PG-B');
SET IDENTITY_INSERT GH_ExportSeasons OFF;

-- 2) Seed markets
SET IDENTITY_INSERT GH_ExportMarkets ON;
INSERT INTO GH_ExportMarkets (ID, Name) VALUES
    (1, 'North Market'),
    (2, 'South Market'),
    (3, 'East Market'),
    (4, 'West Market');
SET IDENTITY_INSERT GH_ExportMarkets OFF;

-- 3) Seed market stats (WeekID, SeasonID, MarketID, CropID, WeightbyAir, WeightbySea)
INSERT INTO GH_MarketStats (WeekID, SeasonID, MarketID, CropID, WeightbyAir, WeightbySea) VALUES
    -- Season 1 (PG-A)
    (1, 1, 1, 'CRP1', 10.000000, 4.000000),
    (2, 1, 1, 'CRP1', 9.000000, 3.000000),
    (3, 1, 1, 'CRP1', 8.500000, 2.500000),
    (1, 1, 2, 'CRP2', 7.000000, 1.000000),
    (2, 1, 2, 'CRP2', 6.500000, 1.200000),
    (3, 1, 2, 'CRP2', 7.500000, 1.800000),
    -- Season 2 (PG-B)
    (1, 2, 3, 'CRP1', 11.000000, 2.500000),
    (2, 2, 3, 'CRP1', 13.000000, 2.700000),
    (3, 2, 3, 'CRP1', 14.000000, 2.800000),
    (1, 2, 4, 'CRP1', 12.000000, 3.000000);
6

this is working

DECLARE @Cols VARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);

declare @ProductGroupID VARCHAR(50) = 'PG-A'
declare @SeasonID INT = 1
declare @CropID  VARCHAR(50) = 'CRP1'

SELECT 
    @Cols = COALESCE (@Cols + ',[' + convert(varchar,MS.WeekID) + ']', '[' + convert(varchar,MS.WeekID) + ']') 
FROM 
    GH_MarketStats MS
             inner join GH_ExportSeasons ES on ES.ID=MS.SeasonID
             inner join GH_ExportMarkets EM on EM.ID=MS.MarketID
WHERE 
  ES.ProductGroupID = @ProductGroupID
GROUP BY
     MS.WeekID
ORDER  BY
    MS.WeekID;

SELECT @SQL = CONCAT('SELECT * FROM (',
        'SELECT EM.[Name], MS.WeekID, (MS.WeightbyAir + MS.WeightbySea) as Tot',
        ' FROM GH_MarketStats MS',
        ' INNER JOIN GH_ExportSeasons ES on ES.ID = MS.SeasonID',
        ' INNER JOIN GH_ExportMarkets EM on EM.ID = MS.MarketID',
        ' WHERE ES.ProductGroupID = ''', @ProductGroupID, '''',
        ' AND MS.SeasonID = ', @SeasonID,
		' AND MS.CropID = ''', @CropID, '''',
  ') AS SourceTable',
  ' PIVOT (',
        ' SUM(Tot)',
        ' FOR WeekID IN (',@cols, ')',
 ' ) AS PivotTable'
);
   EXEC SP_EXECUTESQL @SQL

image