Monthwise report With Pivot Query

http://impulsecode.blogspot.com/2011/06/monthwise-report-with-pivot-query.htmlhi Friends,

Today I come arcross a very nice  dyanmic  query example  on Priyank's  blog. So I would like to repost same  details  here


 There are some specific forms list in one table. There is one more table for month. The status of form as per month recorded in another table. Base on status table, we have to find for which month status record not fill up for forms.

Table Name         Field Name
Forms                   form_id
                              form_name

Months                 Month_id
                              Month_Name

Status                   form_id
                              month_id
                              status (whether filled or not)
                              comment (Comment for each entry)

Sample Data As per Bellow: 

Required Result

I found one solution for this. The code of query as per bellow.

create table Forms(formId int, formName varchar(20)) 

create table Months(MonthId int,[Month] varchar(20))
create table StatusTable(FormId int,MonthId int,Status varchar(10),
Comment varchar(10))

insert into Forms
Select 1,'Form1' union all
Select 2,'Form2' union all
Select 3,'Form3'

insert into Months
Select 1,'Jan-11' union all
Select 2,'Feb-11' union all
Select 3,'Mar-11' union all 
Select 4,'Apr-11' union all 
Select 5,'May-11' 


insert into StatusTable
select 1,1,'Yes','Good' union all
select 1,2,'Yes','Bad'  union all
select 1,3,'Yes','Excellent'  union all
select 2,2,'Yes','Good' union all
select 2,3,'Yes','Bad'  union all
select 2,4,'Yes','Excellent'  union all
select 3,3,'Yes','Bad'  union all
select 3,4,'Yes','Excellent'  union all
select 3,5,'Yes','BAD'

declare @query varchar(MAX)
declare @query2 varchar(MAX)
declare @query3 varchar(MAX)
select @query2 = COALESCE(@query2 + ', ','') + QUOTENAME(MONTH) from MONTHS
select @query3 = COALESCE(@query3 + ', ','') + QUOTENAME(MONTH)+' AS '
+QUOTENAME(MONTH) from MONTHS
set @query=';With CTE as 
(select F.FORMID,F.FORMNAME,M.MONTH,ISNULL(S.STATUS,''NO'') as STATUS 
from FORMS F
cross join MONTHS M
left join StatusTable S on F.FORMID=S.FORMID and M.MONTHID=S.MONTHID)
select FORMNAME,'+@query3+' from cte 
PIVOT (max(STATUS) for MONTH IN ('+@query2+')) pvt'
exec(@query)


drop table Forms
drop table Months
drop table StatusTable