Take a look at below example:
IF NOT OBJECT_ID(N'#Sale',N'U') IS NULL
DROP TABLE #Sale
CREATE TABLE #Sale (ProductName VARCHAR(30), CountOfPosition INT, TotalPrice FLOAT, SaleDate DATETIME)
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 1005, 5000.12, '2012-01-14')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 1030, 5312.88, '2012-01-22')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 998, 4987.65, '2012-01-31')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 1012, 5100.12, '2012-02-01')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 1099, 5719.78, '2012-02-16')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 888, 4567.32, '2012-02-28')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 1005, 5000.12, '2012-03-05')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 1030, 5312.88, '2012-03-12')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 898, 4987.65, '2012-03-29')
DECLARE @cols VARCHAR(1000)
DECLARE @dt VARCHAR (2000)
DECLARE @pt VARCHAR(4000)
SET @cols = '[1],[2],[3]'
SET @dt = 'SELECT ProductName, TotalPrice, MONTH(SaleDate) AS SaleMonth ' +
'FROM #Sale ' +
'WHERE YEAR(SaleDate) = 2012 '
SET @pt = 'SELECT ProductName, ' + @cols + ' ' +
'FROM (' + @dt + ') AS DT ' +
'PIVOT (SUM(TotalPrice) FOR SaleMonth IN (' + @cols + ')) AS PT ' +
'ORDER BY PT.ProductName'
EXEC (@pt)
DROP TABLE #Sale
Result:
ProductName 1 2 3
Fruits 15300,65 15387,22 15300,65
As you see, calculations are provided for each product (in this case only fruits) and for each month of year 2012.
To get total sum for each product, we need to change pivot query, removing
ORDER BY
clause:
SET @pt = 'SELECT ProductName, ' + @cols + ' ' +
'FROM (' + @dt + ') AS DT ' +
'PIVOT (SUM(TotalPrice) FOR SaleMonth IN (' + @cols + ')) AS PT '
EXEC (@pt)
DECLARE @fq VARCHAR(4000)
DECLARE @cols2 VARCHAR (200)
SET @cols2 = '[1] + [2] + [3]'
SET @fq = 'SELECT ProductName, ' + @cols + ', ' + @cols2 + ' AS TotalSum ' +
'FROM (' + @pt + ') AS FQ'
EXEC (@fq)
More at:
Using PIVOT and UNPIVOT[
^]
Search this site to find more examples.