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 we 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 table records.
SELECT * FROM ItemDetails
As you can see, a table are having three unique Item named as ‘Scanner’ , ‘Hard disk’ and ‘Pen Drive’ .
Our goal is to display the sum of total Item Quantity purchased in a each month for an individual Item ,Where unique month will be displayed in a columns.
As you have to display a sum of quantity purchased in each month for an individual item ,so you need to get month first from ItemDetails table .
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 purchased year and month of items. Here we have done nothing just got a month year from itempurchasedate.
Now , we modify the above SQL statement and use PIVOT to display the sum of purchased quantity for an individual item in each month as column wise .
Lets look at the following statement which uses PIVOT and tranform 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, unique month values of MnthYr column is transformed into multiple columns of Month in the result set and display the Total purchased quantity of an individual Items in each month in 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.
Recommended for you
1,970 total views, 5 views today