http://impulsecode.blogspot.com/2011/06/monthwise-report-with-pivot-query.html hi 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:
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 table
create 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 tableinsert 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 tableinsert 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
5 comments:
Hello, this weekend is faѕtiԁious іn suррort οf
me, as this tіme i am reаdіng this gгеat educatіonal paгagгaрh heгe
at my home.
my weblog - abrir cuenta facebook
My partner and I absolutely lovе yоur blog anԁ
find many of your post's to be exactly what I'm looking for.
Would you offer guest wгitеrs tο write cοntent for
you personally? I ωоulԁn't mind creating a post or elaborating on a lot of the subjects you write regarding here. Again, awesome weblog!
Check out my site ... Facebook cuenta gratis
Heya i'm for the first time here. I found this board and I in finding It truly useful & it helped me out much. I hope to present one thing back and aid others like you aided me.
Also visit my web-site - nokia 808 specification
Greаt goodѕ from уou, man. I've understand your stuff prior to and you are just too wonderful. I actually like what you have bought here, certainly like what you're stating and the
wау іn whiсh wherein уou say it.
You mаke it entertainіng and you continue to taκe care of to keep it wise.
Ӏ can not ωait to learn muсh moгe from you.
Thаt is really a wonderful sіte.
Look into my weblog: abrir cuenta facebook
Hello mates, good post and good urging commented at this place,
I am really enjoying by these.
Check out my blog post ... golf buddy world platinum gps review
Post a Comment