Solved

Oracle SQL: Create Nested Table Dataset (like COLLECT)

Posted on 2011-09-22
2
535 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
2 Comments
 
LVL 73

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

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

'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 …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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.
This video shows how to recover a database from a user managed backup

809 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