My goal here is to compile/concatenate text from multiple lines for each unique rep_id. Each rep_id can range from 1 to over 20 lines with the max of 255 characters in each one. My first temp table has the results of other data, now I want to include the full report text for each record. declare @rep_id rep_id_type declare @the_full_report varchar(16384), @one_line varchar(255), @line_count int, @end tinyint select @end= 0, @the_full_report = "", @line_count = 0 create table #daily_read_rep a bunch of code is here to build this table and the #rep_id is the primary key I want to map when I build the full rep_text This is what I have been trying to do to build each rep_id and the full text for each one. I am trying to understand how and when to insert the variable results into another temp table so I can map that to my main temp table. Here is my code after the #daily_read_rep temp table has been built: select @rep_id = #pp_rep_id from #daily_read_rep while @end = 0 begin select @line_count set rowcount 1 select @the_full_report = case when @line_count = 0 then reptxt_text else @the_full_report + " " + reptxt_text end, @line_count = @line_count + 1 from risdb_rch05_stag..report_text t1 where reptxt_rep_id = @rep_id and reptxt_seq_no_s >= @line_count order by reptxt_seq_no_s asc if @@rowcount = 0 select @end = 1 end set rowcount 0 insert into #rep_Text values (@rep_id, @the_full_report) I only get one rep_id when I run the #rep_text temp table. Why don't I get the all the other ones? I must be doing something out of sequence here because the one record I get looks good and just what I need. So I need to map the #rep_text to my #daily_read_rep table to get a full result set. I found this reference that I believe is close to what I am trying to accomplish, but can't seem to figure it out with mine. Any help or input is GREATLY appreciated. http://www.sql-server-performance.com/articles/per/operations_no_cursors_p3.aspx
Try: select @the_full_report = case when @line_count = 0 then reptxt_text else ISNULL(@the_full_report,"") + " " + reptxt_text end,
Thank you so much for your valuable time and patience. This does work, but I still only receive one result and should receive over 130 in this set. I seem to be using the insert variable values at the wrong time and can't seem to figure out where I am going wrong? No matter what I do, I get the same thing. Can you help me understand where and how I can populate all the records after each row for the @rep_id and @the_full_report has been built with the full text into the #rep_Text temp table?
don't need @line_count and beside increment @line_count within select is indeterministic. select @the_full_report = isnull(@the_full_report,"") + case when isnull(@the_full_report,"") <> "" then " " else "" end + reptxt_text from risdb_rch05_stag..report_text t1 where reptxt_rep_id = @rep_id and reptxt_seq_no_s >= 0 order by reptxt_seq_no_s asc
It seems that this approach will only pull the last sentence or so and yet will pull the full text as I need it with my original approach. But I still only get one result and I just do not understand why that is. How do I build all of the variable values into a table from this while statement and not just the first one? For some reason I am only mapping to one #pp_rep_id where I have the reptxt_rep_id = @rep_id. Do I need to set a max rep_id from my table table then do a while @rep_id < that max value as this compiles?
Here is almost working pseudocode - good lucks: declare @id int, @rep_id rep_id_type , @the_full_report varchar(16384) create table #daily_read_rep -- <=== Add an 'id' identity(int,1,1) column, use that to loop thru this temp table /* a bunch of code is here to build this table and the #rep_id is the primary key I want to map when I build the full rep_text This is what I have been trying to do to build each rep_id and the full text for each one. I am trying to understand how and when to insert the variable results into another temp table so I can map that to my main temp table. */ Here is my code after the #daily_read_rep temp table has been built: select @rep_id = #pp_rep_id from #daily_read_rep set @id = 0 while 1 > 0 begin select @rep_id = isnull(rep_id,0), @id = isnull(id,0) from #daily_read_rep where id = @id + 1 if (@id <1) break SELECT @the_full_report = "" select @the_full_report = isnull(@the_full_report,"") + case when isnull(@the_full_report,"") <> "" then " " else "" end + reptxt_text from risdb_rch05_stag..report_text where reptxt_rep_id = @rep_id and reptxt_seq_no_s >= 0 order by reptxt_seq_no_s asc insert into #rep_Text values (@rep_id, @the_full_report) end -- while