Skip to Main Content
  • Questions
  • Unnest a nested table with the extracted data in single row

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Kartick.

Asked: November 22, 2021 - 5:40 am UTC

Last updated: December 01, 2021 - 11:38 am UTC

Version: 12c

Viewed 100+ times

You Asked

Hi,

I have a nested table with three columns within the nested column. I have 3 entries for the same ID within the nested column. I want to unnest this table and get the 3 entries as separate columns in single row. How do I do it?

Below is the code:

create or replace TYPE           "TEST" AS OBJECT
(
 point       NUMBER(3),
 latitude      NUMBER(10),
 longitude      NUMBER(10)
)

create or replace TYPE           "TESTS" IS TABLE OF TEST;

  CREATE TABLE TEST_TABLE
   ( "ID" NUMBER(3,0), 
  "LOCATION" "SYS"."TESTS" 
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" 
 NESTED TABLE "LOCATION" STORE AS "LOCATIONS"
 (PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 NOCOMPRESS 
  TABLESPACE "SYSTEM" ) RETURN AS VALUE;

Insert into TEST_TABLE (ID,LOCATION) values (161,SYS.TESTS(SYS.TESTS(0, 4009716, 50056416), SYS.TESTS(1, 4324450, 51769233), SYS.TESTS(2, 5570283, 51604983), SYS.TESTS(3, 5845666, 49989300)));
Insert into TEST_TABLE (ID,LOCATION) values (162,SYS.TESTS(SYS.TESTS(0, 4862133, 43994149), SYS.TESTS(1, 3183550, 43960533), SYS.TESTS(2, 3970383, 45314300), SYS.TESTS(3, 5032600, 44909200)));


Expected Output:
ID   POINT1   LATITUDE1 LONGITUDE1 POINT2  LATITUDE2  LONGITUDE2   POINT3   LATITUDE3  LONGITUDE3  POINT4  LATITUDE4  LONGITUDE4
---  -------  --------- ---------- ------  ---------  ----------   ------   ---------  ----------  ------  ---------  ----------
161  0        4009716   50056416   1       4324450    51769233     2        5570283    51604983    3       5845666    49989300
162  0        4862133   43994149   1       3183550    43960533     2        3970383    45314300    3       5032600    44909200

and Chris said...

Use the table operator to unnest it into rows:
select id, l.* 
from   test_table, table ( location ) l;

        ID      POINT   LATITUDE  LONGITUDE
---------- ---------- ---------- ----------
       161          0    4009716   50056416
       161          1    4324450   51769233
       161          2    5570283   51604983
       161          3    5845666   49989300
       162          0    4862133   43994149
       162          1    3183550   43960533
       162          2    3970383   45314300
       162          3    5032600   44909200


Pivot this to turn the rows into columns:

with rws as (
  select id, l.* 
  from test_table, table ( location ) l
)
select * from rws
pivot (
  max ( point ) point,
  max ( latitude ) latitude, 
  max ( longitude ) longitude
  for point in ( 0, 1, 2, 3 )
);

        ID    0_POINT 0_LATITUDE 0_LONGITUDE    1_POINT 1_LATITUDE 1_LONGITUDE    2_POINT 2_LATITUDE 2_LONGITUDE    3_POINT 3_LATITUDE 3_LONGITUDE
---------- ---------- ---------- ----------- ---------- ---------- ----------- ---------- ---------- ----------- ---------- ---------- -----------
       161          0    4009716    50056416          1    4324450    51769233          2    5570283    51604983          3    5845666    49989300
       162          0    4862133    43994149          1    3183550    43960533          2    3970383    45314300          3    5032600    44909200


This assumes the points are all consecutive starting at zero. If this isn't the case, you can use row_number in the subquery to assign consecutive numbers starting at one. Then pivot on this.

Rating

  (3 ratings)

Comments

Kartick T, November 24, 2021 - 11:40 am UTC

Hi Chris,

Thanks a lot for this. It worked perfectly. But what if the points (0,1,2,3) are not the same in all rows. Lets say in another row it is (4,6,7,8) or random values in each line. How do we approach the situation then?
Chris Saxon
November 24, 2021 - 2:13 pm UTC

As I said in the answer:

you can use row_number in the subquery to assign consecutive numbers starting at one

This maps 4, 6, 7 => 1, 2, 3

Kartick T, November 26, 2021 - 9:13 am UTC

Hi Chris,

But what if it is not points or consecutive numbers at all. What if it is random characters.
Chris Saxon
December 01, 2021 - 11:38 am UTC

You can use any sortable data type in row number - put the relevant column(s) in the order by, it generates the numbers for you.

Then use the result of this in your pivot.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library