SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

shahid00704
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.

Thanks,

madhivanan
Premature Yak Congratulator

India
22769 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.

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.05 seconds. Powered By: Snitz Forums 2000