We are in the process of doing a data migration from an old AS400 into a new Oracle database. I am using SQL to do the data transformation. I have this table:
CREATE TABLE [PARTDOC] (
[IICONO] [decimal](2, 0) NOT NULL ,
[IIPTNO] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IISQ03] [decimal](3, 0) NOT NULL ,
[IID30A] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
Here is a small subset of the data:
SELECT IICONO, IIPTNO, IISQ03, IID30A
FROM PARTDOC
ORDER BY IICONO, IIPTNO, IISQ03
ICONO IIPTNO IISQ03 IID30A
1 3650012 1 W/4- 15/16" 0 HOLES & 1
1 3650012 2 9/16" 0 HOLE
1 3650013 1 15/16" HOLES & 2-9/16" 0 HOLES
1 3650015 1 W/6- 15/16" 0 HOLES
1 3650016 1 W/8- 15/16" 0 HOLES
1 3650016 3 PAINT GRAY
2 2020319 1 CONSISTS OF:
2 2020319 2 12" HELPER PLATE
2 2020319 3 6" HELPER PLATE
2 2020319 5 MUST BE SENT TO ENGINEERING
2 2020319 6 FIRM TO BE TESTED PRIOR TO
2 2020319 7 SENDING TO FIELD
2 2050036 1 FLEET MANAGEMENT PROGRAM
2 3601843 1 12" X 12" X 1/4" X 25'6
2 3601843 2 OVER ORDERED FOR A JOB
3 3601908 1 FOR SAUSAGE CAULK
3 3650002 1 15/16" 0 HOLE & W/2 PL'S
3 3650002 2 2 1/2" X 1/2" X 2 1/2"
3 3650003 1 15/16" 0 HOLES & W/2 PL'S
3 3650003 2 8" X 1/2" X 0' -6"
This is how I need it to look:
DESIRED RESULT
ICONO IIPTNO IID30A
1 3650012 W/4- 15/16" 0 HOLES & 1 /^CR^/ 9/16" 0 HOLE
1 3650013 15/16" HOLES & 2-9/16" 0 HOLES
1 3650015 W/6- 15/16" 0 HOLES
1 3650016 W/8- 15/16" 0 HOLES /^CR^/PAINT GRAY
2 2020319 CONSISTS OF: /^CR^/12" HELPER PLATE /^CR^/6" HELPER PLATE /^CR^/MUST BE SENT TO ENGINEERING /^CR^/FIRM TO BE TESTED PRIOR TO /^CR^/SENDING TO FIELD
2 2050036 FLEET MANAGEMENT PROGRAM
2 3601843 12" X 12" X 1/4" X 25'6 /^CR^/OVER ORDERED FOR A JOB
3 3601908 FOR SAUSAGE CAULK
3 3650002 15/16" 0 HOLE & W/2 PL'S /^CR^/2 1/2" X 1/2" X 2 1/2"
3 3650003 15/16" 0 HOLES & W/2 PL'S /^CR^/8" X 1/2" X 0' -6"
I am thinking that I will need to use a cursor but I've never created one quite this complex and I can't seem to get my head around it.
Any help would be greatly appreciated!!
BTW - if the ICONO = 2 then I need to make 2 new rows -one row with ICONO = 21, and another with ICONO = 22