0

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 IBOR  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 ...

Robert
  • 25,425
  • 8
  • 67
  • 81
Srilesh
  • 123
  • 1
  • 1
  • 7
  • Ah, So you don't have the `LIST` function. Doesn't look promising from this question unless you can create user defined functions or aggregates in Sybase? http://stackoverflow.com/questions/491566/how-to-get-list-of-values-in-group-by-clause – Martin Smith Aug 11 '10 at 21:56
  • The table from which I have to extract has around 100 columns and millions of rows. Apart from using cursors which will not be an optimal solution, I want to try evaluating any other approach you suggest. Even writing a stored procedure or function is acceptable. – Srilesh Aug 12 '10 at 00:11

1 Answers1

3

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

Tim
  • 6,406
  • 22
  • 34
  • I have tried the above query, but it takes really long and I had to abort. I have extracted the 4 columns into a temp table "my_table", with index on B to run the query. – Srilesh Aug 12 '10 at 14:45
  • Any suggestions on using temp tables and updates to achieve this. – Srilesh Aug 12 '10 at 14:57