CODEWITHSUNDEEP
sap-ase
Goldbones
Goldbones

Reputation: 1457

Concanate rows on Sybase by Id

I´m having results on a table like:

enter image description here

What I wanted to do is have result like:

enter image description here

How can I do this because Sybase don t have concatenate method?

Upvotes: 1

Views: 776

Answers (1)

markp-fuso
markp-fuso

Reputation: 34244

setup:

create table mytab
(id varchar(10)
,b  varchar(30))
go

insert mytab values ('233.263','DDDD')
insert mytab values ('233.263','AAAAAQ')
insert mytab values ('233.283','22223')
insert mytab values ('233.283','333333')
go

-- #lists will hold intermediate results
create table #lists
(id     varchar(10)
,b_list varchar(1000))
go

declare our cursor:

NOTE: OP did not state how the b column values are to be sorted so we'll stick with alphabetical sorting for this example.

declare mycur cursor
for
select id,b
from   mytab
order by id,b
for read only
go

loop through cursor, appending b values to @b_list for each distinct id:

declare @id     varchar(10),
        @id_old varchar(10),
        @b      varchar(30),
        @b_list varchar(1000)

select  @id_old = NULL

open mycur

fetch mycur into @id, @b

while @@sqlstatus = 0
begin
    -- if new @id value, save old @id/@b_list pair
    if @id != isnull(@id_old,'UNDEFINED')
    begin
        if @b_list is not NULL
        begin
            insert #lists values (@id_old, @b_list)
        end

        select  @id_old = @id,
                @b_list = NULL
    end

    select  @b_list = @b_list + case when @b_list is not NULL then '|' end + @b

    fetch mycur into @id, @b
end

-- save last @id/@b_list pair
if @b_list is not NULL
begin
    insert #lists values (@id, @b_list)
end

close mycur

deallocate cursor mycur
go

display results:

select * from #lists
go

 id      b_list
 ------- ------------
 233.263 AAAAAQ|DDDD
 233.283 22223|333333

Upvotes: 2

Related Questions