SQL PIVOT allows us to aggregate the results and rotate rows into columns. In sort, using PIVOT you can transform data from row-level to columnar data.
SYNTAX
SELECT column1, column2,..., [pivotvalue1], [pivotvalue2], ... FROM table_name PIVOT ( aggregatefunction(aggcoulmnname) ) FOR (pivotcolumn) IN ([pivotvalue1], [pivotvalue2]) ) as pvt
column1, column2,…, are displayed as the first columns in the pivot table.
pivotvalue1, pivotvalue2.. are values to pivot.
aggregatefunction is a SQL valid aggregate functions such as MAX, MIN ,AVG ,SUM or COUNT .
aggcoulmnname is a column is to be used with the aggregatefunction .
pivotcolumn is the column that contains the pivot values.
pvt is a alias for the pivot table.
Lets look at an example of SQL PIVOT.
First, create a sample table named as ItemDetails , then insert some records into table.
CREATE TABLE ItemDetails (RowId INT, Item VARCHAR(50), ItemQty INT, ItemPurchaseDate DATETIME) INSERT INTO ItemDetails (RowID, Item, ItemQty, ItemPurchaseDate) VALUES (1, 'Scanner',300, '2019-12-01'), (2, 'Scanner',250, '2019-12-10'), (3, 'Scanner',110, '2019-11-12'), (4, 'Scanner',110, '2019-09-19'), (5, 'Scanner',280, '2019-09-25'), (6, 'Scanner',180, '2019-08-15'), (7, 'Scanner',180, '2019-07-08'), (8, 'Pen Drive',360, '2019-12-15'), (9, 'Pen Drive',270, '2019-09-11'), (10, 'Pen Drive',210, '2019-08-22'), (11, 'Pen Drive',190, '2019-08-19'), (12, 'Pen Drive',480, '2019-07-15'), (13, 'Pen Drive',680, '2019-06-25'), (14, 'Pen Drive',120, '2019-06-18'), (15, 'Hard Disk',270, '2019-09-19'), (16, 'Hard Disk',210, '2019-08-12'), (17, 'Hard Disk',190, '2019-08-29'), (18, 'Hard Disk',480, '2019-07-25'), (19, 'Hard Disk',680, '2019-06-15'), (20, 'Hard Disk',120, '2019-06-28')
Now, lets fetch a records from table.
SELECT * FROM ItemDetails
As you can see, a table is having three unique item named as ‘Scanner’, ‘Hard disk’ and ‘Pen Drive’.
Assume that, you want to display the sum of total item quantity purchased in a each month for each item, where unique month will be displayed in a columns.
As you have to display a sum of quantity purchased in each month for each item. So you need to get month from date.
SELECT RowId, Item, ItemQty, DATENAME(MONTH, ItemPurchaseDate)+'-' + CAST(YEAR(ItemPurchaseDate) AS VARCHAR) AS MNTHYR FROM ItemDetails
As you can see, it returns an Item, ItemQty and MnthYr which is a year and month of item purchased.
Here, we have done nothing just get a month year from date Itempurchasedate.
Now, we modify the above SQL statement and use PIVOT to display the sum of purchased quantity for each item by month as column.
Lets look at the following statement which uses PIVOT and transform the row level data into columnar data.
SELECT Item , ISNULL([January-2019],0) AS [January-2019], ISNULL([Feburary-2019],0) AS [Feburary-2019],
ISNULL([March-2019],0) AS [March-2019], ISNULL([April-2019],0) AS [April-2019], ISNULL([May-2019],0) AS [May-2019],
ISNULL([June-2019],0) AS [June-2019], ISNULL([July-2019],0) AS [July-2019], ISNULL([August-2019],0) AS [August-2019],
ISNULL([September-2019],0) AS [September-2019], ISNULL([October-2019],0) AS [October-2019],
ISNULL([November-2019],0) AS [November-2019], ISNULL([December-2019],0) AS [December-2019]
FROM (
SELECT Item, ItemQty, DATENAME(MONTH, ItemPurchaseDate)+'-' + CAST(YEAR(ItemPurchaseDate) AS VARCHAR) AS MNTHYR
FROM ItemDetails
) AS itemtbl
PIVOT ( SUM(ItemQty)
FOR MNTHYR IN (
[January-2019], [Feburary-2019],[March-2019], [June-2019],[April-2019],
[May-2019], [July-2019], [August-2019], [September-2019], [October-2019], [November-2019], [December-2019]
)
) AS pvt
As you can see above result set, after using pivot a unique month values of MnthYr column is transformed into multiple columns of month in the result set and display the Total purchased quantity of each item in month as columns.
You can also create a dynamic pivot query, in this case you do not need to pass hardcoded month year value in pivot query.
Also Read..
