Dynamic SQL and Pivot Example using SQL Server

Dynamic SQL and Pivot Example 

Before going through below code, Download adventureworks database for sql server 2008 r2

In this example, i am going to show how many employees joined in the employee based on from date and to date. Here i have used PIVOT and Dynamics sql.

After below stored procedure execution. run

spRangaRajeshDynamic '','2003-01-15','2003-01-20'

Code:

create procedure spRangaRajeshDynamic (@id int, @fromDate date, @toDate date)
as
begin
declare @_MonthDay varchar(max)
declare @_query varchar(max)
declare @fromDateCharacters varchar(30)
declare @toDateCharacters varchar(30)

set @fromDateCharacters = convert(varchar(30), cast(@fromDate as date), 102)
set @toDateCharacters = convert(varchar(30), cast(@toDate as date), 102)

Select @_MonthDay=COALESCE(@_MonthDay+']'+',[' , '[') + 
datename(mm,HireDate)+ ' '+cast(day(HireDate) as varchar) 
from [AdventureWorks2008R2].[HumanResources].[Employee] 
WHERE HireDate BETWEEN @fromDate AND @toDate 
--'2003-01-15' and '2003-01-20'
Group by 
 --year(HireDate),
 month(HireDate),
 day(hiredate),
 datename(mm,HireDate)
set @_MonthDay =@_MonthDay+']'
--select @_MonthDay

set @_query=
'select * from 
(Select --Year(HireDate) as year, 
datename(mm,HireDate)+ '' ''+cast(day(HireDate) as varchar) MonthDay,
--count(JobTitle) as TotalEmployees,
JobTitle
from [AdventureWorks2008R2].[HumanResources].[Employee] 
WHERE HireDate BETWEEN '''+@fromDateCharacters+''' AND '''+@toDateCharacters+'''
Group by 
 month(HireDate),
 day(hiredate),
 datename(mm,HireDate),
 JobTitle
) 
 p
 PIVOT (count(jobtitle) for 
 MonthDay in ('+@_MonthDay+') 
 --([January 15],[January 16],[January 17],[January 18],[January 19],[January 20])
 ) as pvt'
 
 exec(@_query);
 
end

Output:

Dynamic SQL and Pivot Example using SQL Server