Display multiple values of a column in one row (SQL Oracle) [duplicate]

Question

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?

There are other questions (seeking to do the same thing) which have already been answered. Check out stackoverflow.com/questions/4370820/… — Jeffrey Blake
– Jeffrey Blake, Commented Dec 14, 2011 at 16:38

Dave Costa · Accepted Answer · 2011-12-14 16:38:39Z

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

answered Dec 14, 2011 at 16:38

Dave Costa

48.3k8 gold badges61 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

BvilleBullet Over a year ago

your assumptions are correct I will try this out thanks for the really fast reply

Jeffrey Blake Over a year ago

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.

BvilleBullet Over a year ago

question ... would 'assoc_table' be where I would place my table name in this SQL query? All the data resides in one table.

Dave Costa Over a year ago

Yes, that would be the table name

BvilleBullet Over a year ago

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

Collectives™ on Stack Overflow

Display multiple values of a column in one row (SQL Oracle) [duplicate]

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related