a	Hi
x	how
x	are
x	you
b	Fine
x	thank
x	you

a	Hi	Hi
x	how	Hi how
x	are	Hi how are
x	you	Hi how are you
b	Fine	Fine
x	thank	Fine thank
x	you	Fine thank you

quote:

---

Originally posted by malawneh

DECLARE @ConcatField VarChar(1000)

SELECT @ConcatField = ISNULL(@ConcatField,'') + ' ' + ColumnB
FROM tblBuild
WHERE ColumnA = 'a'

Michael Alawneh, DBA

---

tenant1	200000	1	1/31/2007	memo1 line1
tenant1	200001	1	99/31/2007	memo1 line2
tenant1	200002	1	99/31/2007	memo1 line3
tenant1	200003	1	2/08/2007	memo2 line1
tenant1 200004	1	99/08/2007	memo2 line2
tenant2 200000	1	1/04/2005	memo3 line1
tenant2	200001	1	99/04/2005	memo3 line2

quote:

---

Originally posted by yosiasz

yes there is/are way(s). Question for you though are the dates in sequential order , for the same tenant could there be identical dates. what constitues a new group, which column field. in your example tenant1 has two bogus dates so both are last lines. Also what is that second column: is that a sequence column?

---

create table #workingtable (
	tcode int not null,
	indnum int,
	startdate varchar(10),
	memo varchar(50),
	newind int,
	newdate varchar(10),
	fullmemo varchar(500),
	flag int)

insert into #workingtable (tcode,indnum,memo,startdate)
	select tcode,indnum,memo,date from import_data.dbo.ldmanh#text$ where stat = 1 order by tcode,indnum

declare
	@fullmemo varchar(500),
	@lastt int,
	@newind int,
	@newdate varchar(10)

select
	@fullmemo = '',
	@lastt = -1,
	@newind = -1,
	@newdate = ''

update
	#workingtable
set 
	flag = case
			when @lastt <> tcode or len(@fullmemo)>450 or left(startdate,2) <> '99' then 1
			else null
			end,
	@fullmemo = fullmemo = case
				when @lastt <> tcode or len(@fullmemo)>450 or left(startdate,2) <> '99' then rtrim(memo)
				else @fullmemo + ' ' + rtrim(memo)
				end,
	@newind = newind = case
				when @lastt <> tcode or len(@fullmemo)>450 or left(startdate,2) <> '99' then indnum
				else @newind
				end,
	@newdate = newdate = case
				when left(startdate,2) <> '99' then startdate
				else @newdate
				end,
	@lastt = tcode


create table #workingtable2 (
	tcode int not null,
	indnum int,
	startdate varchar(10),
	memo varchar(500))

	declare crsr cursor fast_forward for (select tcode, newind, startdate, newdate, fullmemo,flag from #workingtable)
	declare
		@tcode int,
		@index int,
		@date varchar(10),
		@fullmemo2 varchar(500),
		@nexttcode int,
		@nextindex int,
		@nextdate varchar(10),
		@nextmemo varchar(500),
		@keydate varchar(10),
		@flag int
	select
		@fullmemo2 = '',
		@tcode = -1,
		@index = -1,
		@date = '',
		@nexttcode = -1,
		@nextindex = -1,
		@nextdate = '',
		@nextmemo = '',
		@keydate = '',
		@flag = -1
	open crsr
	fetch crsr into @nexttcode, @nextindex, @keydate, @nextdate, @nextmemo,@flag
	while @@fetch_status = 0
	begin
		if @flag = 1	
			begin
				insert into #workingtable2(tcode, indnum, startdate, memo) values (@tcode,@index,@date,@fullmemo2)
				select @fullmemo2 = @nextmemo, @tcode = @nexttcode, @index = @nextindex, @date = @nextdate
			end
		else
			select @fullmemo2 = @nextmemo, @tcode = @nexttcode, @index = @nextindex, @date = @nextdate
		fetch crsr into @nexttcode, @nextindex, @keydate, @nextdate, @nextmemo, @flag
	end

	close crsr
	deallocate crsr

	select * from #workingtable2 order by tcode, indnum

go