Sybase ASE Transpose Indefinite Number of Rows to Columns

Question

I have tried doing pivot tables but only for fixed number of rows.

I have the following records:

ID   CODE
===  ====
1    AAA
1    BBB
1    CCC
2    DDD
3    EEE
3    FFF
4    GGG
4    HHH
4    III
4    JJJ

And my expected result is:

ID   CODE1  CODE2  CODE3  CODE4
===  =====  =====  =====  =====
1    AAA    BBB    CCC
2    DDD
3    EEE    FFF
4    GGG    HHH    III    JJJ

Take note that the number of rows returned per id is not fixed. I want to avoid cursor as much as possible.

Answer 1

To do it without a loop, you need to adding an artificial row number, for example through an identity column. If you do not want to change your schema, copy the whole table into a temp table first. (I didn't check for syntax errors but you'll get the idea)

alter table yourtab add seq int identity not null

select id, min_seq=min(seq) into #t from yourtab group by id

select id=max(id), code1=max(code1), code2=max(code2),
code3=max(code3), (etc) from ( select     id = yourtab.id,   
code1=case (yourtab.id-#t.min_seq) when 0 then code else null end,   
code2=case (yourtab.id-#t.min_seq) when 1 then code else null end,   
code3=case (yourtab.id-#t.min_seq) when 2 then code else null end,  
[...etc...] 
from yourtab, #t where yourtab.id = #t.id order by
yourtab.id ) as newtab