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 73

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle RAC 12c 8 60
How to simplify my SQL statement? 14 50
check the deletion of SQL job on who delete/disable it 12 29
database upgrade 8 43
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

867 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now