Hello Gurus,

I have a requirement like below..

T1 table

ID code
001 1
001 2
002 1
003 4
003 1

Output should be

T2 table

ID code
001 1,2
002 1
003 1,4


I tried with cursors in Sybase... like below...

alter proc sp_test_sample
as
begin
DECLARE @k char(8), @id_var char(8), @cd_var VARCHAR(10)

DECLARE c CURSOR FOR
select id,code from party_sample order by id,code

create table #work
(
id_work char(8), cd_work varchar(200))

select @k ="-1"

OPEN c

FETCH c INTO @id_var, @cd_var

while(@@sqlstatus=0)
begin

if @id_var != @k
insert into #work (id_work, cd_work) values (@id_var,@cd_var)
else
update #work set cd_work = cd_work+','+@cd_var where id_work = @id_var


print 'hello %1! , %2!' , @id_var,@cd_var

select @k = @id_var

FETCH c INTO @id_var, @cd_var


end

CLOSE c

DEALLOCATE c

============


However as sybase is not a row processor like oracle,.. its taking lot of time.. Could you please suggest an alternative solution using temp tables or something similar which will be faster in Sybase...?

I am using Sybase IQ.. List() is not a valid command FYI

this should solve your problem...
select ID as "c_id" , (select list(code) from T1 where ID = "c_id") from T1

List is not a valid function in Sybase IQ. Any other options which will be faster for concatenating rows into column?

Hi,
Please try this option

DECLARE @LastID Varchar(3),
@LastCode VARCHAR(12)

SELECT ID,CODE,CONVERT(VARCHAR(25),'') as RESULT
INTO #T1 FROM T1 order by ID,CODE

UPDATE #T1
SET RESULT = CASE
WHEN ID = @LastID THEN @LastCode+','+CODE
ELSE CODE
END,
@LastID = ID,
@LastCode = CODE

SELECT ID,MAX(RESULT) FROM #T1 GROUP BY ID
GO