Dynamic SQL and Pivot ExampleBefore 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
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
|Dynamic SQL and Pivot Example using SQL Server|