Link to home
Start Free TrialLog in
Avatar of globalwm2
globalwm2Flag for United States of America

asked on

Oracle SQL: Create Nested Table Dataset (like COLLECT)

I have a table join where there are multiple rows per my join column name. Using the COLLECT function on each column:

SELECT inventory_item_id, COLLECT (promodisplayorder) AS display_order, COLLECT (promoname) AS promo_name 
FROM WEBSTAGE_promo
GROUP BY inventory_item_id) 

Open in new window


will roll-up these values - each in a nested table (single column). Is there a way to have a single column with a double-column nested table ?

For example:

col1, col2
        subcol1, subcol2

An example of this can been seen by the USER_DATA column in:
select * from wf_deferred

Open in new window


I'm just looking for two columns though.
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of globalwm2

ASKER

Perfect - thanks for the lesson.