Oracle SQL: Create Nested Table Dataset (like COLLECT)

Posted on 2011-09-22
Last Modified: 2012-05-12
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 
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.
Question by:globalwm2
LVL 74

Accepted Solution

sdstuber earned 350 total points
ID: 36584842
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

                  (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

Author Closing Comment

ID: 36588079
Perfect - thanks for the lesson.

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS SQL Update query with connected table data 3 38
Currency in SQL? 2 30
Toad 12.10 Enterprise visual interface 4 22
Trouble with <> 2 21
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question