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
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 44909200Pivot 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 44909200This 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.