I have written quite a few articles about PIVOT but this is somehow bit different than all of the previous article, the need is to create dynamic pivot table which shows month name as a header for country and display the count of sales amount. Main thing is to display month name as a header where as we have month number in data, moreover, it should be dynamic PIVOT as # of month is not fixed, may be 1 or 2 or 12 (can’t have more than 12 -;) )

Let us create one dummy table along with data to start our PIVOT journey.

--table for demo

create table testing

(

country varchar(10),

[Month] int,

Amount int

)

go

--dummy data

insert into testing

select 'A-land',6,100 union all

select 'B-Land',5,110  union all

select 'B-Land',7,90  union all

select 'C-Land',6,200  union all

select 'C-Land',2,70  union all

select 'D-Land',8,30

GO

--PIVOT script

DECLARE @Cols NVARCHAR(2000)

SET @Cols=''

--collect distinct month we have in table

SELECT @Cols=@Cols+ '['+DATENAME(month,convert(varchar,s.[month])+'-1-1900') +']'+ ', ' FROM

(SELECT DISTINCT [MONTH] FROM testing ) AS s order by s.[Month]

--remove last comma

SET @Cols=LEFT(@Cols,LEN(@Cols)-1)

--our pivot will display Month name though we have Month number in table.

SET @Cols='SELECT * from (select country,DATENAME(month,convert(varchar,[month])+ ''-1-1900'') as [Month] ,amount FROM testing) up

PIVOT (count(amount) for [month] in ('+@cols+')) AS pivo'

--execute our dynamic query resides in @COL variable

EXECUTE sp_executeSQL @Cols

GO

If you want to look at my other PIVOT example than do look those at:

http://www.sqlhub.com/2009/03/dynamic-pivot-with-where-condition-in.html

http://www.sqlhub.com/2009/03/dynamic-cross-tab-query-without-cursor.html

http://www.sqlhub.com/2009/03/dynamic-cross-tab-query-with-cursor-in.html

http://www.sqlhub.com/2009/03/pivot-fixed-column-cross-tab-query-in.html

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

I have written many articles on PIVOT even I feel to write one more on practical situation. Suppose you have one table which contain studentID, year of student, total marks, increase and decrease. We need to find list of student on increase and decrease. How can we do that?

Have a look at this example with dummy data.

CREATE TABLE StudDetail

(

StudentID INT,

PassYear VARCHAR(10),

Grades int,

Increase INT,

Decrease INT

)

GO

INSERT INTO StudDetail

SELECT 1, '08-09', 3333,0,0 union all

SELECT 1, '09-10', 4252,25,0 union all

SELECT 2, '08-09', 2100,0,0 union all

SELECT 2, '09-10', 2002,0,-10

GO

--pivot query

SELECT * from (select StudentID,PassYear,grades FROM StudDetail where increase>=0) up

PIVOT (sum(grades) for passyear in ([08-09], [09-10])) AS pivo

--I can work with above query but what if I don't know how much year I have to make column???

--I have to go for dynamic query, let us create it.

--START creating dynamic code for PIVOT

DECLARE @Cols NVARCHAR(2000)

SET @Cols=''

--make column list for PIVOT

SELECT @Cols=@Cols+ '['+s.Passyear +']'+ ', ' FROM

(SELECT DISTINCT PassYear FROM StudDetail) AS s

--remove last comma from column list

SET @Cols=LEFT(@Cols,LEN(@Cols)-1)

--create pivot query as we have just added distinct year list in @Cols variable

SET @Cols='SELECT * from (select StudentID,PassYear,grades FROM StudDetail where increase>=0) up

PIVOT (sum(grades) for passyear in ('+@cols+')) AS pivo'

--print query and check

print @cols

EXECUTE sp_executeSQL @Cols

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

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

SELECT 'MOUSE','NY',2000

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

PIVOT

(

sum(TotalSales) FOR state in (CA,NJ)

)AS pivo

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