none
Pivot table data and get sum for each activity.

    Question

  • 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(count) approved_gurantees(sum)   approved_funding(sum)         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
    
    

    the same i have asked here

    if the above data is not properly shown please check this

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=188066


    can somebody help me in writing this query.



    • Edited by dastagir Sunday, September 08, 2013 7:58 PM code
    Sunday, September 08, 2013 7:54 PM

Answers

  • Try this,

    declare @year int
    set @year = 2011
    ;with cte as (
    	select [Year],Activity,
    	Count(Guarantee_no) [No. of Guarantee],
    	Sum(Approved_gurantee_amount) Approved_gurantees,
    	Sum(Approved_funding_amount) Approved_funding from (
    		select *,year(date_gurantee) as [Year] from #guarantee a inner join #activity b on b.ID = a.[ActivityID]
    	) X
    	where [Year] >= @year and [Year] <= Year(getdate())
    	group by [Year],Activity 
    ),cte1 as (
    	select [Year],
    	Activity + '/No. of Guarantee' [Activity1],
    	Activity + '/Approved_Gurantees' [Activity2],
    	Activity + '/Approved_Funding' [Activity3],
    	[No. of Guarantee],Approved_gurantees,Approved_funding
    	 from cte
    ),cte2 as (
    	select * from cte1
    	pivot
    	(
    	 max([no. of Guarantee]) for Activity1 In ([Industry/No. of Guarantee],[Commerce/No. of Guarantee],[Construction/No. of Guarantee])
    	) pvt
    	pivot
    	(
    	 max(Approved_Gurantees) for Activity2 In ([Industry/Approved_Gurantees],[Commerce/Approved_Gurantees],[Construction/Approved_Gurantees])
    	) pvt1
    	pivot
    	(
    	 max(Approved_Funding) for Activity3 In ([Industry/Approved_Funding],[Commerce/Approved_Funding],[Construction/Approved_Funding])
    	) pvt3
    )
    select [Year],
    Max([Industry/No. of Guarantee]) [Industry/No. of Guarantee],
    Max([Industry/Approved_Gurantees]) [Industry/Approved_Gurantees],
    Max([Industry/Approved_Funding]) [Industry/Approved_Funding],
    Max([Commerce/No. of Guarantee]) [Commerce/No. of Guarantee],
    Max([Commerce/Approved_Gurantees]) [Commerce/Approved_Gurantees],
    Max([Commerce/Approved_Funding]) [Commerce/Approved_Funding],
    Max([Construction/No. of Guarantee]) [Construction/No. of Guarantee] ,
    Max([Construction/Approved_Gurantees]) [Construction/Approved_Gurantees],
    Max([Construction/Approved_Funding]) [Construction/Approved_Funding]
    from cte2
    group by [Year]


    Regards, RSingh

    • Marked as answer by dastagir Monday, September 09, 2013 6:06 AM
    Monday, September 09, 2013 5:39 AM

All replies

  • Follow the dynamic PIVOT example:

    http://www.sqlusa.com/bestpractices2005/dynamicpivot/

    For such a complex crosstabulation report, best to use Reporting Services (SSRS).


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Sunday, September 08, 2013 9:40 PM
    Moderator
  • Try this,

    declare @year int
    set @year = 2011
    ;with cte as (
    	select [Year],Activity,
    	Count(Guarantee_no) [No. of Guarantee],
    	Sum(Approved_gurantee_amount) Approved_gurantees,
    	Sum(Approved_funding_amount) Approved_funding from (
    		select *,year(date_gurantee) as [Year] from #guarantee a inner join #activity b on b.ID = a.[ActivityID]
    	) X
    	where [Year] >= @year and [Year] <= Year(getdate())
    	group by [Year],Activity 
    ),cte1 as (
    	select [Year],
    	Activity + '/No. of Guarantee' [Activity1],
    	Activity + '/Approved_Gurantees' [Activity2],
    	Activity + '/Approved_Funding' [Activity3],
    	[No. of Guarantee],Approved_gurantees,Approved_funding
    	 from cte
    ),cte2 as (
    	select * from cte1
    	pivot
    	(
    	 max([no. of Guarantee]) for Activity1 In ([Industry/No. of Guarantee],[Commerce/No. of Guarantee],[Construction/No. of Guarantee])
    	) pvt
    	pivot
    	(
    	 max(Approved_Gurantees) for Activity2 In ([Industry/Approved_Gurantees],[Commerce/Approved_Gurantees],[Construction/Approved_Gurantees])
    	) pvt1
    	pivot
    	(
    	 max(Approved_Funding) for Activity3 In ([Industry/Approved_Funding],[Commerce/Approved_Funding],[Construction/Approved_Funding])
    	) pvt3
    )
    select [Year],
    Max([Industry/No. of Guarantee]) [Industry/No. of Guarantee],
    Max([Industry/Approved_Gurantees]) [Industry/Approved_Gurantees],
    Max([Industry/Approved_Funding]) [Industry/Approved_Funding],
    Max([Commerce/No. of Guarantee]) [Commerce/No. of Guarantee],
    Max([Commerce/Approved_Gurantees]) [Commerce/Approved_Gurantees],
    Max([Commerce/Approved_Funding]) [Commerce/Approved_Funding],
    Max([Construction/No. of Guarantee]) [Construction/No. of Guarantee] ,
    Max([Construction/Approved_Gurantees]) [Construction/Approved_Gurantees],
    Max([Construction/Approved_Funding]) [Construction/Approved_Funding]
    from cte2
    group by [Year]


    Regards, RSingh

    • Marked as answer by dastagir Monday, September 09, 2013 6:06 AM
    Monday, September 09, 2013 5:39 AM
  • Thank you very much RSingh.
    Monday, September 09, 2013 6:06 AM