如何将存储过程输出从列转换为sql中的行 [英] how to convert the store procedure output from columns into rows in sql

问题描述

在我的商店程序输出中，我想从列转换为行。





我的商店程序代码如下

In my store procedure output, i want to convert from columns into rows.


My store procedure code as follows

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[Presea_RefresherSMS] 
as
begin

declare @Course varchar(20),
@NoofStudents varchar(20),
@Rowcount int,
@batchid varchar(20),
@CourseDate varchar(10)

select @CourseDate =CONVERT(VARCHAR(10), GETDATE(), 101)


create table #TempTable (course varchar(10), Noofstudents varchar(10))

begin tran
declare courses Cursor for


select cmn_minor_code as Course_Name,cbm_batch_id as Batch_ID from co_batch_master where cbm_active <> 'D' and cbm_batch_start_dt = @Coursedate and cmn_minor_code in('RFPFF','R-AFF','RFPFF_C')



open courses
fetch next from courses into @Course,@batchid
while @@Fetch_status = 0 
begin
begin tran 


declare studentcount cursor for
select count(*) from batch_course_registration a,course_registration b
where b.cr_bill_no = a.cr_bill_no and a.bcr_batch_id = @batchid and b.cr_active = 'A'
open studentcount
fetch next from studentcount into @Rowcount
while @@Fetch_status = 0
begin
insert into #TempTable values(@Course,@Rowcount)
fetch next from studentcount into @Rowcount
end
close studentcount
deallocate studentcount
commit tran
fetch next from courses into @Course,@batchid
end
close courses
Deallocate courses
commit tran
select * from #TempTable

当我运行商店程序时显示输出如下

When i run the store procedure shows output as follows

Course Noofstudents
RFPFF 16
R-AFF 16
RFPFF_C 6

i want the output as follows
RFPFF 16,R-AFF 16,RFPFF_C 6 

请帮帮我。

我该怎么办



问候，

Narasiman P.

please help me.
for that how can i do

Regards,
Narasiman P.

推荐答案

你必须Pivot [ ^ ]您的数据

这篇关于如何将存储过程输出从列转换为sql中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！