Here's one way to do it:
- Repeat the into table clause for each column you want to become a row
- For each one, specify a filler for the columns you don't want to load
- For the 2nd, 3rd, etc. loads, specifying position(1) for the first column to reset the location to the start of the row
- You can use sequence to set row numbers; I'm not clear exactly how you want to do this though
SQL> create table t (
2 id int,
3 c1 varchar2(1),
4 c2 varchar2(1),
5 c3 varchar2(1),
6 c4 varchar2(1)
7 );
Table created.
SQL> ho type sqlldr.ctl
LOAD DATA
INFILE *
APPEND
INTO TABLE "T"
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(id sequence(1,1),
c1, c2, f3 filler, f4 filler
)
INTO TABLE "T"
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(id sequence(3,1),
c1 position(1), f2 filler, c3, f4 filler
)INTO TABLE "T"
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(id sequence(5,1),
c1 position(1), f2 filler, f3 filler, c4
)
BEGINDATA
A|B|C|D|
A|E|F|G|
SQL> ho sqlldr userid=chris/xxxxx@db control=sqlldr.ctl
SQL*Loader: Release 12.2.0.1.0 - Production on Fri Jan 19 10:43:27 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 2
Table "T":
2 Rows successfully loaded.
Table "T":
2 Rows successfully loaded.
Table "T":
2 Rows successfully loaded.
Check the log file:
sqlldr.log
for more information about the load.
SQL> select * from t;
ID C C C C
---------- - - - -
1 A B
2 A E
3 A C
4 A F
5 A D
6 A G