Generic stored procedure for PIVOT in SQL Server

Well, so far I have written few small example of PIVOTing the data in SQL Server and thought that now this is enough about PIVOT, I won’t write anything more about PIVOT but when I seen one good stored procedure for dynamic PIVOT  in expert exchange forum written by my friend and very experienced person Mr. Mark Wills I tempted again to share PIVOT material with my reader.
Let us FIGHT THE FEAR OF PIVOT with SQLHub.com
Here is the article written by Mr. Mark Wills. I am sure my blog reader will like his article very much.
SQL 2005 Dynamic Pivot Query
By Mark Wills

PIVOT is a great facility and solves many an EAV (Entity – Attribute – Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively easy to do, other times it can be a challenge (and that is why we are here).
Let’s have a quick look at the PIVOT function…
SELECT <display_column_list>
FROM
          (SELECT <source_columns> as Column_Source
                         ,<column_to_be_aggregated> as Column_Value
                         ,<column_with_new_column_names> as Column_List
           FROM <datasource> ) as DataSource
PIVOT
          (<aggregate_function>(Column_Value)  FOR  Column_List  IN
          ([<new_column_1_heading>],[<new_column_2_heading>],…,[<new_column_N_heading>]) ) PivotTable
ORDER BY <column_number_or_name>;
That looks pretty straight forward, except for one or two small details:

1) First up, we need to know the “display_column_list”
    easy enough, just do a Select * instead and problem solved (except that it does control display sequence)
 2) Secondly, we need to know and hard code the new column headings as in :
     ([<new_column_1_heading>],[<new_column_2_heading>],…,[<new_column_N_heading>])
 And that last point is often the big challenge. Not so bad if we are doing something static like “months in a year”, just list out those months in sequence, and make sure you can cast the column which contains those new headings accordingly e.g. datename(month,”column_with_new_column_names”)
But, what about a moving target – like “last 3 months” ? Or, an EAV table with unknown attribute names ? Normally, that means we need to rewrite our query every month, or after every change of data.
There is a way, and that involves some Dynamic SQL, more importantly, we can make it a procedure which can handle any “simple” dynamic pivot table.
So, lets get started… but first we need a fairly simple example, so we will create some data accordingly. Feeling generous, we will do two. One is a classic “rolling periods”  and the other a typical EAV
CREATE TABLE tst_CustSales (
   TCS_ID INT Identity Primary Key Clustered,
   TCS_Customer varchar(60),
   TCS_Date DATETIME,
   TCS_Quantity INT,
   TCS_Value MONEY )
GO
CREATE TABLE tst_EAV_Data (
   TED_ID INT Identity Primary Key Clustered,
   TED_Entity varchar(60),
   TED_Attribute varchar(60),
   TED_Value varchar(60) )
GO

— now let’s populate our tst_* tables
INSERT tst_CustSales (TCS_Customer, TCS_Date, TCS_Quantity, TCS_Value)
SELECT * FROM (
SELECT ‘Customer 1’ as Customer,‘20090101’ as Date, 11 as Qty, 1001.00 as Val union all   
SELECT ‘Customer 1’,‘20090201’,12, 1002.00 union all
SELECT ‘Customer 1’,‘20090301’,13, 1003.00 union all
SELECT ‘Customer 1’,‘20090401’,14, 1004.00 union all
SELECT ‘Customer 2’,‘20090101’,21, 2001.00 union all
SELECT ‘Customer 2’,‘20090201’,22, 2002.00 union all
SELECT ‘Customer 2’,‘20090301’,23, 2003.00 union all
SELECT ‘Customer 2’,‘20090401’,24, 2004.00 union all
SELECT ‘Customer 3’,‘20090101’,31, 3001.00 union all
SELECT ‘Customer 4’,‘20090201’,42, 4002.00 union all
SELECT ‘Customer 5’,‘20090301’,53, 5003.00 ) as src
GO
— notice I do not mention the Identity Column – SQL will manage that for me
— notice the yyyymmdd “style 112” format – implicitly converts to datetime
— now our EAV table, again imagine some diverse attributes
INSERT tst_EAV_Data (TED_Entity, TED_Attribute, TED_Value)
SELECT * FROM (
SELECT ‘Customer 1’ as Customer,‘Phone’ as Attr,‘+61299991234’ as Data_Val union all
SELECT ‘Customer 1’,‘Address’,’24 Somewhere Street’ union all
SELECT ‘Customer 1’,‘Building’,‘The ReallyTall One’ union all
SELECT ‘Customer 1’,‘Contact’,‘Marcus Aurelius’ union all
SELECT ‘Customer 2’,‘Phone’,‘+61288881234’ union all
SELECT ‘Customer 2’,‘Contact’,‘Ritesh Shah’ union all
SELECT ‘Customer 3’,‘Address’,‘1600 Pennsylvania Avenue’ union all
SELECT ‘Customer 3’,‘Building’,‘The WhiteHouse’ union all
SELECT ‘Customer 4’,‘Phone’,‘+61277771234’ union all
SELECT ‘Customer 4’,‘Address’,‘1 Nile Way union all
SELECT ‘Customer 4’,‘Building’,‘The Pyramids’ union all
SELECT ‘Customer 4’,‘Contact’,‘Cleo Patra’ union all
SELECT ‘Customer 5’,‘Phone’,‘+61277771222’ union all
SELECT ‘Customer 5’,‘Friend’,‘Cleo Patra’ ) as src
GO
— Now we can get down and dirty with the Pivot.
— First we will construct a properly formed one so you can “see” the pivot in action.
SELECT TCS_Customer, [01 Feb 2009],[01 Mar 2009],[01 Apr 2009]
FROM
  (select TCS_Customer, TCS_Date, TCS_Value from tst_CustSales ) sourcedata
PIVOT
  (sum(TCS_Value) for TCS_Date in ([01 Feb 2009],[01 Mar 2009],[01 Apr 2009])) pivottable
GO
— You can see from the above that the column_list and headings are all hard coded…
— Also note how SQL is dynamically converting the datetime to those column headings
— that is because dd MMM yyyy is implicitly converted to datetime in a date context
— but “Style 106” is language dependant,
— and in this case, amazingly, can handle the “hard coded” column names.
— Now let us have a look at some Dynamic SQL for the EAV table, again in “long hand”.
— The dynamic bit is getting those column names so we do not have to hard code them…
DECLARE @Columns varchar(8000)
DECLARE @SQL varchar(8000)
SET @Columns = substring((select ‘,[‘+TED_Attribute+‘]’ from tst_EAV_Data group by TED_Attribute for xml path()),2,8000)
SET @SQL = ‘SELECT * FROM
  (Select TED_Entity as Cust,TED_Attribute,TED_Value from tst_EAV_Data) sourcedata
PIVOT
  (max(TED_Value) for TED_Attribute in (‘+@Columns+‘)) pivottable’
EXEC(@sql)
GO
— Let’s have a look at the above, all we really did was to generate the column list.
— You can try it again replacing the EXEC(@SQL) with Print @SQL
— You will see pretty much the same command structure as the earlier pivot.
— Now to create a Procedure so we can simply keep using a stored procedure
— rather than having to write code all the time. So lets get into it…
CREATE PROCEDURE uDynamicPivot(
                 @sourcedata varchar(8000),
                 @Pivot_On_Source_Column varchar(2000),
                 @Pivot_Value_Aggregate varchar(10),
                 @Pivot_Value_Column varchar(2000),
                 @Pivot_Column_List varchar(2000),
                 @Pivot_Column_Style_Code varchar(4))  — used in convert for style code
AS
BEGIN
— we really should put in some error checking, e.g. if anything is NULL it will crash.
   declare @columns varchar(max)
   declare @sql nvarchar(max)
   set @sql = N‘set @columns = substring((select ”, [”+convert(varchar,’+@Pivot_Column_List+@Pivot_Column_Style_Code+‘)+”]” from ‘+@sourcedata+‘ group by ‘+@Pivot_Column_List+‘ for xml path(””)),2,8000)’
   execute sp_executesql @sql,
                         N‘@columns varchar(max) output’,
                         @columns=@columns output
   set @sql = N‘SELECT * FROM
       (SELECT ‘+@Pivot_On_Source_Column+‘,’+@Pivot_Column_List+‘,’+@Pivot_Value_Column+‘ from ‘+@sourcedata+‘) src
       PIVOT
       (‘+@Pivot_Value_Aggregate+‘(‘+@Pivot_Value_Column+‘) FOR ‘+@Pivot_Column_List+‘ IN (‘+@columns+‘) ) pvt
       ORDER BY 1′
   execute sp_executesql @sql
END
GO
— Now, let’s use that procedure by plugging the parameters needed for a PIVOT function
uDynamicPivot ‘tst_CustSales’,‘TCS_customer’,‘sum’,‘TCS_Value’,‘TCS_Date’,‘,106’

— and the EAV
uDynamicPivot ‘tst_EAV_Data’,‘TED_Entity as Cust’,‘max’,‘TED_Value’,‘TED_Attribute’,
— We can even include some “where” clauses for simple requirements
uDynamicPivot ‘tst_CustSales where TCS_Date >= convert(varchar(6),dateadd(month,-3,getdate()),112)+”01”’,‘TCS_customer’,‘sum’,‘TCS_Value’,‘TCS_Date’,‘,106’
— But that is getting pretty ugly, and that is where the VIEW comes into play…
— VIEWS allow data to be presented in a similar way in which we use a table.
— a view is a good way to present data that does need some kind of transformation
— it also allows a certain detachment from the underlying table.
— views are really a pointer or script to the actual data
— and does not contain data itself more so the “rules” on how to get/show the data.
— once created, it is part of the database and can be re-used as often as you like.
CREATE VIEW vw_last_3_months AS
SELECT TCS_Customer as Customer
,      TCS_Value
,      DATEADD(day, 0, DATEDIFF(day, 0, TCS_Date)) as Date   
,      DATEADD(day, 1, DATEDIFF(day, 0, TCS_Date)) day(TCS_Date) as Start_Of_Month 
,      DATEADD(month, 1, DATEDIFF(day, 0, TCS_Date)) day(TCS_Date) as End_Of_Month
FROM   tst_CustSales
WHERE  TCS_Date >= convert(varchar(6),dateadd(month,-3,getdate()),112)+’01’
GO
— Note how we are using date functions to transform our date data to remove time
— and generate a start and end of month.
— Now we can do our “simple” function call using our View
uDynamicPivot ‘vw_last_3_months’,‘customer’,‘sum’,‘TCS_Value’,‘End_Of_Month’,‘,106’
— And that as they say is that. Please take care when running on your machine
— make sure you check table names,
— double check your code,
— go step at a time,
— hope you have some fun with it.
I would like to request all of my reader, please drop a line with your views about this article.

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

Dynamic PIVOT with month number to month name as header in SQL Server 2008

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:
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

Dynamic PIVOT with WHERE condition in SQL Server 2005

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