2

Possible Duplicate:
Is there an Oracle SQL query that aggregates multiple rows into one row?

I'm trying to to create a SQL query that can return data from a table to display multiple values of a column in one row.

For example this is the table setup: 

SEQ   ROWSEQNUM   ASSISTING_ASSOCIATES  
100   2           19332816  
100   1           1366344  
103   1           12228238  
104   1           1366474

I need to query results to look like this:

 SEQ   ROWSEQNUM   ASSISTING_ASSOCIATES  
 100   1           1366344; 19332816  
 103   1           12228238  
 104   1           1366474

Does anybody have any insight?

Community
  • 1
  • 1
BvilleBullet
  • 201
  • 3
  • 8
  • 17
  • There are other questions (seeking to do the same thing) which have already been answered. Check out http://stackoverflow.com/questions/4370820/is-there-a-way-in-oracle-to-join-multiple-row-lines-into-a-single-one-using-t – Jeffrey Blake Dec 14 '11 at 16:38

1 Answers1

0

I think this ought to work, assuming that for each SEQ value there is always a row with ROWSEQNUM=1 and the values for ROWSEQNUM increase sequentially with no gaps.

select seq, min(rowseqnum), max(assoc_list)
from (
  select seq, rowseqnum, sys_connect_by_path(assisting_associate,';') assoc_list
    from assoc_table
    start with rowseqnum=1
    connect by seq = prior seq and rowseqnum = prior rowseqnum + 1
  )
group by seq
Dave Costa
  • 47,262
  • 8
  • 56
  • 72
  • your assumptions are correct I will try this out thanks for the really fast reply – BvilleBullet Dec 14 '11 at 16:39
  • If this works for you, remember to click the green checkmark to mark it as the accepted answer. That rewards Dave for his efforts to help and shows other users which answer you found to be the best. – Jeffrey Blake Dec 14 '11 at 16:41
  • question ... would 'assoc_table' be where I would place my table name in this SQL query? All the data resides in one table. – BvilleBullet Dec 14 '11 at 18:34
  • Yes, that would be the table name – Dave Costa Dec 14 '11 at 21:27
  • thanks I am still testing tweaking this but thank you very much for the help. I will come back to accept once I know for sure. This query is actually for an oracle based live link system so it's a little different than the standard system – BvilleBullet Dec 15 '11 at 21:06