Link to home
Start Free TrialLog in
Avatar of _Scotch_
_Scotch_

asked on

sql server 2005, appending data, coalesce, is there a better way ?

Hello Experts... I have a table with columns for CaseID and Notes.  There could be anywhere from 1 to hundreds of notes per caseid.  I want to create a new table that has each caseid's associated notes glued altogether in a single field.

I've got something that works, but it doesn't scale well ... The attachment is a reproducer... Am I missing an easier or faster way ?
/*
 
-- Run just once to create the function on your system
 
CREATE function [dbo].[ConcatCaseNotesTest](@pCaseId char(10))
returns varchar(8000)
as
begin
	declare @res varchar(8000)
	select @res = coalesce(@res+' ','') + Notes
		from tmp
		where CaseID = @pCaseId
	return substring(@res,1,7999)
end
 
*/
 
declare @nCases int
declare @nNotes int 
declare @sql varchar(8000)
 
--
-- create example table
--
 
create table tmp (CaseID char(10), Notes varchar(8000))
set nocount on
 
set @nCases = 0
while @nCases <= 10
	begin
		set @nNotes = 0
		while @nNotes <= 10
			begin
				set @sql = 'insert into tmp (CaseID,Notes) values (''' 
					+ convert(varchar, @nCases) + ''''
					+ ', '''
					+ ' text of note ' + convert(varchar, @nNotes) + ' of case ' + convert(varchar,@nCases)
					+ ''')'
				exec (@sql)
				set @nNotes = @nNotes + 1
			end
		set @nCases = @nCases + 1
	end
 
--
-- coagulate the notes fields by caseID
--
create table #tmp2 ( CaseID char(10), Notes varchar(8000) )
 
insert into #tmp2
	select distinct CaseID, dbo.ConcatCaseNotesTest(CaseID) "Notes"
	from tmp 
 
select * from #tmp2 order by convert(int,caseid)
drop table #tmp2
drop table tmp
 
--drop function  [dbo].[ConcatCaseNotesTest]

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of _Scotch_
_Scotch_

ASKER

Sorry it took me so long to get back here - momi's command was close enough to work great !!  The real working command for the example table given is:

select distinct caseid, (select notes as [data()]
      from tmp
      where caseid=t1.caseid for xml path('')) as concatenated_notes
from tmp t1