i have table as follow
key list seqno
1021 abc 1
1021 xyz 2
1021 pqr 3
1022 mno 1
1022 def 2
1022 jkl 3

I want to make a single list corresponding to key.
Like
1021 abcxyzprq
1022 mnodefjkl
I have written some code but i am getting only first list abc not a abcxyzprq

alter procedure cm_list
As
begin
set nocount on
declare
@List varchar(2000),
@List1 varchar(2000),
@List2 varchar(2000),
@seqnbr numeric(12,0),
@key numeric(12,0) ,
@count2 numeric(12,0)

select @count2 = 1

declare list_cursor cursor for
select distinct List, SeqNo
from Table1
where Key = 1021

open list_cursor
fetch list_cursor into @List1,@seqnbr

while @@fetch_status = 0
begin
select @List =''
select @ist = isnull(@List,'')+ @List1
while ( @count2 <= @seqnbr )
begin
FETCH rulelist_cursor into @List1,@seqnbr
select @List = @List+ @List1
select @count2 = @count2 + 1
end
end

select @List
close list_cursor
deallocate list_cursor
return
end
GO
Plz tell me how I can get the following ouptput
1021 abcxyzprq
1022 mnodefjkl

I think you need to rephrase your post, its very confusing!

Try building a temp table, then in your loop while you're fetching rows from the cursor, either insert a new one if your key has changed, or update the current row and concatenate your text column.

Hi,

You can get use of Coalesce function here.

I named the table as Letters and columns as id and letters.

First create a function named ConcatenateStr() as below

CREATEFUNCTION ConcatenateStr (
@Id int
)
RETURNSnvarchar(255)
AS
BEGIN
DECLARE @str ASnvarchar(255)
SELECT
@str =COALESCE(@str,'')+ Letters
FROM Letters
WHERE Id = @Id
RETURN @str
END

Then you can call this function in the below select command

selectdistinct id, dbo.ConcatenateStr(id)from letters

I hope this will solve your problem

You can check the below article for coalesce
http://www.kodyaz.com/articles/artic...x?articleid=29

Eralper