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 main table

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

-- create month table

create table Months(MonthId int,[Month] varchar(20))

-- create status tablecreate table StatusTable(FormId int,MonthId int,Status varchar(10),
Comment varchar(10))

-- insert in 1st table
insert into Forms
Select 1,'Form1' union all
Select 2,'Form2' union all
Select 3,'Form3'

-- insert in 2nd table
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 in 3rd table
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 @qry varchar(MAX)
declare @qry2 varchar(MAX)
declare @qry3 varchar(MAX)
select @qry2 = COALESCE(@qry2 + ', ','') + QUOTENAME(MONTH) from MONTHS
select @qy3 = COALESCE(@qry3 + ', ','') + QUOTENAME(MONTH)+' AS '
+QUOTENAME(MONTH) from MONTHS
set @qry=';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,'+@qry3+' from cte 
PIVOT (max(STATUS) for MONTH IN ('+@qry2+')) pvt'
exec(@qry)

-- drop all table
drop table Forms
drop table Months
drop table StatusTable