How do I get one row in the result set out of 2 rows in the table/file without using cursors in Sybase/Perl

Question

I have a table T with columns A,B,C & D as below:

A,B,C,D
A1,B1,USD,LIBOR
A1,B2,JPY,FIXED
A2,B3,USD,LIBOR
A2,B4,EUR,FIXED
A3,B5,JPY,FIXED
A3,B6,USD,LIBOR

1. Table always has 2 rows per "A", with different values of "B".

2. "B" is unique

3. "A" - varchar(10), "B" - varchar(8), "C" - varchar(3), "D" - varchar(5)

4. Validation rules restrict the columns to the below formats - "A", "B', "D" - Alphanumeric "C" - Alphabetic

I need to extract the below results from the table -

Result Set 1:
A1,USD,LIBOR,JPY,FIXED
A2.USD,LIBOR,EUR,FIXED
A3,JPY,FIXED,USD,LIBOR

Result Set 2:
USD,LIBOR,JPY,FIXED
USD,LIBOR,EUR,FIXED

I wish to avoid using cursors, unless there any other solution.

Can I extract this table into a file and use perl to get the result ?

Please help ...

Answer 1

It can be done with temporary tables and multiple updates, but I don't think that's going to be very nice in your million line table.

If your data always has 2 rows per "A", with different values of "B" and has predictable sizes (column widths) or format (so you can use special delimators) then something like this might work

SELECT A, MIN(B), MAX(B), right( MIN(B + C), 5), right( MIN(B + D), 5), right( MAX(B + C), 5), right(MAX(B + D), 5) FROM my_table GROUP BY A