Click here to Skip to main content
16,016,022 members
Please Sign up or sign in to vote.
4.33/5 (2 votes)
See more:
My store procedure has the following code:

SQL
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[Keyword_Course_SMS](@Keyword varchar(10))
as
begin

declare @Batchdate varchar(20),
        @Coursefees money
create table #Temptable (Batchdate varchar(20),Coursefees varchar(10)) 

begin tran
declare course cursor for 
select a.cmn_minor_code,b.Keyword from CO_MINOR_MASTER as a,Tb_Course_Keyword as b where 
a.cmn_minor_code = b.Keyword and b.Active <> 'D';

open course

   declare coursedate cursor for
            select top 1 isnull(crm_course_rate,0) from CO_BATCH_MASTER where cmn_minor_code = @Keyword
                and crm_active <>'D' and (datediff(d, crm_eff_start_dt, getdate()) >= 0) and 
                (datediff(d, getdate(), crm_eff_end_dt) >= 0)

 open coursedate 
   fetch next from coursedate into @Batchdate
   while @@Fetch_status = 0
         begin
           begin tran 

             declare fees cursor for
                select top 1 isnull(crm_course_rate,0) from co_rate_master where cmn_minor_code = @Keyword
                and crm_active <>'D' and (datediff(d, crm_eff_start_dt, getdate()) >= 0) and 
                (datediff(d, getdate(), crm_eff_end_dt) >= 0)
              open fees 
         fetch next from fees into @Coursefees
               while @@Fetch_status = 0
			   begin
                 begin tran
                   insert into #Temptable values(@Batchdate,@Coursefees)
               fetch next from fees into @Coursefees
               end 
                close fees
				deallocate fees
			    commit tran

            fetch next from coursedate into @Batchdate
            end
            close coursedate
            deallocate coursedate
            commit tran

          close course
          deallocate course
          commit tran
          select * from #Temptable
          end

This is how I execute the above store procedure:

SQL
exec [Keyword_Course_SMS] 'APS'

And here is the output:

Batchdate      Coursefees
24Feb2014  8900
26Feb2014  8900



But I want the above output as follows

Batchdate      Coursefees
24Feb2014     8900  26Feb2014     8900


What changes I have to made for getting the above output?

Regards,
narasiman P.
Posted
Updated 2-Feb-14 19:01pm
v2
Comments
chaau 3-Feb-14 1:04am    
Please read about PIVOT: http://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900