Window ?

Hi,

I have a road segment table with a few attributes for each. For each segment, I have a road index and a segment index something like :

road    seg     colA

1        1        att1

1        2        att2

1        3        att3

1        4        att4

2        1        att5

2        2        att6

I want to convert records into lines,

1        att1    att2    att3    att4

2        att5    att6    ...

I was considering using window function, with a partition on road but the problem is that segment count is different for each road, up to 30. So it's seems a bit rough to write something like

select nth_value(colA,1), nth_value(colA,2), nth_value(colA,3), nth_value(colA,4)...

Of course I can write a script with a loop to insert segments one road after the other, but before going to this, I would appreciate a smarter idea !

Thanks