我希望行中的输出不使用存储过程在列中 [英] i want the output in row not in column using store procedure

问题描述

我的商店程序如下

My store procedure as follows

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[Shortcode_Accommodation] (@Keyword varchar(50))
as

declare @Hotel varchar(25),
@Phone varchar(25),
@Room varchar(25),
@final varchar(max)
 
create table #TempTable(res varchar(max))  

begin
insert into #Temptable 
select ('Dear Students, Hotel Accommodation is ' + Hotel_Name + ',' +
Phoneno + ',' + Room_Rate +  ' By HIMT') as res from Tb_Hotel_Accommodation where Active <> 'D' and Description = @Keyword

select * from #Temptable 
end

我执行时的
商店程序输出如下

when i execute the store procedure output as follows

exec [Shortcode_Accommodation]  'ACCOMMODATION'

亲爱的学生，酒店住宿是SATKAR LODGE，9380197278 / 26425881/33570477，单人非AC 425，双人非AC 525，双人间AC 1145由HIMT



亲爱的学生，酒店住宿是HOTEL RAAJ BHAAVAN，26404621/22，单人非AC 900，双人非AC 1200，单人AC 1100，双人AC 1200由HIMT







但是当我执行商店程序时我想要输出我想要如下



亲爱的学生，酒店住宿是1.SATKAR LODGE，9380197278/26425881/33570477，单人非AC 425，双人非AC 525 ，双人AC 1145,2.RAAJ BHAAVAN，26404621/22，单人非AC 900，双人非AC 1200，单人AC 1100，双人AC 1200由HIMT



从我上面的商店程序中我犯了什么错误。



问候，

narasiman P.

Dear Students, Hotel Accommodation is SATKAR LODGE,9380197278/26425881/33570477,Single Non-AC 425,Double Non-AC 525,Double AC 1145 By HIMT

Dear Students, Hotel Accommodation is HOTEL RAAJ BHAAVAN,26404621/22,Single Non-AC 900,Double Non-AC 1200,Single AC 1100,Double AC 1200 By HIMT



But i want when i execute the store procedure output i want as follows

Dear Students, Hotel Accommodation is 1.SATKAR LODGE,9380197278/26425881/33570477,Single Non-AC 425,Double Non-AC 525,Double AC 1145,2.RAAJ BHAAVAN,26404621/22,Single Non-AC 900,Double Non-AC 1200,Single AC 1100,Double AC 1200 By HIMT

from my above store procedure what mistake i made.

regards,
narasiman P.

推荐答案

您所做的是将开始和结束部分插入每一行。因此它们出现在每条线上。你应该只将它们插入 #Temptable 一次。

What you do is that you insert beginning and ending parts to every line. therefore they show up for each line. you should insert them into #Temptable only once.

insert into #Temptable(res) values('Dear Students, Hotel Accommodation is');
insert into #Temptable(res)
select CONVERT(varchar, ROW_NUMBER() over(order by Hotel_Name))+'. '+Hotel_Name+','+Phoneno+','+Room_Rate
from #Tb_Hotel_Accommodation
insert into #Temptable(res) values('by Ozan');

select * from #Temptable

您可以使用 ROW_NUMBER（）函数，如下所示： http://technet.microsoft.com/en-us/library/ms186734.aspx [ ^ ]

you can use ROW_NUMBER() function as here: http://technet.microsoft.com/en-us/library/ms186734.aspx[^]

这篇关于我希望行中的输出不使用存储过程在列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！