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.
globalwm2Asked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
you need to define a SQL type to build the collection on
then you simply invoke the constructor for the type within the collect call


CREATE OR REPLACE TYPE promo_display_name AS OBJECT
                  (display_order INTEGER, promo_name VARCHAR2(100));


SELECT inventory_item_id, COLLECT(promo_display_name(promodisplayorder, promoname)) AS promos
  FROM webstage_promo
GROUP BY inventory_item_id
0
 
globalwm2Author Commented:
Perfect - thanks for the lesson.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.