Solved

Oracle SQL: Create Nested Table Dataset (like COLLECT)

Posted on 2011-09-22
2
538 Views
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 
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
Comment
Question by:globalwm2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 74

Accepted Solution

by:
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


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
 

Author Closing Comment

by:globalwm2
ID: 36588079
Perfect - thanks for the lesson.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

726 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