Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Pivot table data and get sum for each activity.
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

Saudi Arabia
11 Posts

Posted - 09/08/2013 :  15:49:53  Show Profile  Reply with Quote
Hello all,

i am trying to get a query but not getting how to write. I have 2 tables namely activity and guarantee and its columns and data will be like this.

create table #activity
	ID int,
	Activity varchar(50)    

insert into #activity (ID,Activity)
 select 1,'Industry' union all
 select 2,'Construction' union all
 select 3,'commerce' union all
 select 4,'Tourism and recreation' union all
 select 5,'Transport, storage and cooling' union all
 select 6,'Food industry' 
 select * from #activity
 drop table #activity
 create table #guarantee
	Guarantee_no int,
	ActivityID int,
	Approved_gurantee_amount int,
	Approved_funding_amount int,
	date_gurantee datetime
 insert into #guarantee
 select 100,1,20000,22000,'2011-10-05'	 union all
 select 101,1,15000,20000,'2011-08-05' union all
 select 102,2,30000,35000,'2011-09-04' union all
 select 103,2,50000,50000,'2011-10-10' union all
 select 104,3,15000,18000,'2011-10-15' union all
 select 105,3,60000,65000,'2011-10-20' union all
  select 100,1,10000,12000,'2012-10-05'	 union all
 select 101,1,8000,10000,'2012-08-05' union all
 select 102,2,20000,20000,'2012-09-04' union all
 select 103,2,30000,40000,'2012-10-10' union all
 select 104,3,15000,18000,'2012-10-15' union all
 select 105,3,60000,65000,'2012-10-20' union all
  select 100,1,12000,15000,'2013-01-05'	 union all
 select 101,1,30000,40000,'2013-01-05' union all
 select 102,2,15000,20000,'2013-03-04' union all
 select 103,2,30000,30000,'2013-03-10' union all
 select 104,3,15000,18000,'2013-05-15' union all
 select 105,3,60000,65000,'2013-06-20' 
 select * from #guarantee
 drop table #guarantee

from my page i will pass only year.

here in example if i send 2011 then i should get data for 2011,2012 and current year 2013 separated by each activity data. activityid is foreign key in my guarantee table. i want to calculate each activity data yearly. the result will be something like this.

Year                             industry                                                             construction                        .... remaining activities....                                     Total
	     No.of gurantees    approved_gurantees   approved_funding         No.of gurantees    approved_gurantees   approved_funding                                    No.of gurantees    approved_gurantees   approved_funding
2011             2                    35000              42000                         2                  80000               85000                                                  6                  190000              210000
2012             2                    18000              22000                         2                  50000               60000                                                  6                  143000              165000
2013             2                    42000              55000                         2                  45000               50000                                                  6                  162000              188000

can somebody help me in writing this query.


Premature Yak Congratulator

22864 Posts

Posted - 11/05/2013 :  06:28:19  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
No. Multidimensional Report data can not be produced in SQL. Use a reporting tool to achieve the result.


Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000