Sometime we may need to see some data of one column as a column header and aggregated results in those columns as a data. There are many programmatic ways to cater this need. Herewith, I am going to explained Fixed Column Cross Tab results with PIVOT. I will write few more articles for other ways of doing the same task.
Here is the table, I will be using for demo.
Create Table SalesSummaryOfRegions
Item VARCHAR(10) NOT NULL,
State VARCHAR(10) NOT NULL,
TotalSales INT NOT NULL
INSERT INTO SalesSummaryOfRegions
SELECT ‘LAPTOP’,‘CA’,100 UNION ALL
SELECT ‘LAPTOP’,‘NJ’,1200 UNION ALL
SELECT ‘ADAPTER’,‘CA’,910 UNION ALL
SELECT ‘MOUSE’, ‘NY’,1100 UNION ALL
Now, I want to get list of all the Items of NJ and CA with its total and average sales. It seems quiet easy as we can use simple GROUP BY clause and SUM aggregate function. But what if I want to see four column named Item, CA, NJ, Average.
Item column should contain all the Items we used to sell. CA column contain total of each item we sold in CA state in respective item’s row and same with NJ. Average column should contain average of particular item sold for both the state. Not you can say, its bit challenging. Yes, it is, if you are going to do it with sub query logic or cursor. But with the help of PIVOT in Microsoft SQL Server 2005, it is very simple. Have a look at following query.
SELECT Item,CA,NJ,(isnull(CA,0)+isnull(NJ,0))/2 as ‘Average’ FROM SalesSummaryOfRegions
sum(TotalSales) FOR state in (CA,NJ)
In above query, we gave Item from SalesSummaryOfRegions table in select list but we don’t have CA and NJ column in our table. Rest assure, it won’t show you error as it was not written by mistake, it’s a use of PIVOT. You can see I use one aggregate function after PIVOT words in above query and it was used for state column. As will be showing average also, so there is one possibility of NULL value as respective value so I want to make sure that NULL value will be treated as 0 that is why I have used isnull() function.
Reference: Ritesh Shah