• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 557
  • Last Modified:

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.
0
globalwm2
Asked:
globalwm2
1 Solution
 
sdstuberCommented:
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now