Wrapping report text - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Wrapping report text

Author

Topic

natoake
Starting Member

2 Posts

Posted - 2009-01-20 : 10:25:54

I need some help refining my SQL code that is used to wrap the text of reports. In my original dataset, I have multiple rows of text per 1 report. I would like to wrap the text so that the output dataset has 1 row per report with all of the report text in one value. Also, there are certain characters that I would like removed when I am wrapping the text. For example, I would like to replace 3 consecutive dashes and underscores with 1 space.

Here is the code below that wraps the text, but does not remove those characters. Can someone suggest how to incorporate the removal of those characters into the following code?
create procedure
R0804RCSS.sp_ReportSummary(@fromSvcWID integer,@toSvcWID integer)
on exception resume
begin
declare end_of_cursor exception for sqlstate value '02000';
declare @Count integer;
declare @PrevSvcWID integer;
declare @CurrentSvcWID integer;
declare @CurrentText varchar(255);
declare @LongText varchar(31999);
declare outer_cursor dynamic scroll cursor for select a.svcWID,a.svrtLineText from R0804RCSS.tempServiceReportText as a where
svcWID >= @fromSvcWID and svcWID <= @toSvcWID order by
svcWID asc,svrtLineNum asc;
set @Count=0;
set @PrevSvcWID=0;
open outer_cursor;
outer_loop: loop
fetch next outer_cursor into @CurrentSvcWID,@CurrentText;
if sqlstate = end_of_cursor then
update R0804RCSS.servicesReportsTextRollup set fullReportText = @LongText where
svcWID = @PrevSvcWID;
leave outer_loop
end if;
set @Count=@Count+1;
if @CurrentSvcWID = @PrevSvcWID then
set @LongText=@LongText+' '+TRIM(@CurrentText)
else
if @Count <> 1 then
update R0804RCSS.servicesReportsTextRollup set fullReportText = @LongText where
svcWID = @PrevSvcWID
end if;
set @LongText=@CurrentText;
set @PrevSvcWID=@CurrentSvcWID
end if
end loop outer_loop;
close outer_cursor;
commit work;
select svcWID from R0804RCSS.servicesReportsTextRollup where length(fullReportText) >= 31999
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 11:20:13

use replace() to remove unwanted characters. for wrappingtext, do they come from same field or multiple fields?