If you use an external table you can then use unpivot to convert your columns to rows.
For example, given the following file:
a,b,c,d,k1,v1,k2,v2
e,f,g,h,k1,v1,k2,v2,k3,v3,k4,v4
You can create an external table with the "missing field values are null" clause:
create table t (
c1 varchar2(2),
c2 varchar2(2),
c3 varchar2(2),
c4 varchar2(2),
k1 varchar2(2),
v1 varchar2(2),
k2 varchar2(2),
v2 varchar2(2),
k3 varchar2(2),
v3 varchar2(2),
k4 varchar2(2),
v4 varchar2(2)
) organization external (
type oracle_loader
default directory tmp
access parameters
(
records delimited by newline
fields terminated by ','
missing field values are null (
c1 , c2 , c3 , c4 ,
k1 , v1 , k2 , v2 ,
k3 , v3 , k4 , v4
)
)
location ('test.csv')
);
Then use the unpivot operator to turn the key/value pairs into rows:
select * from t
unpivot ((k, v) for c in (
(k1, v1) as 'R1',
(k2, v2) as 'R2',
(k3, v3) as 'R3',
(k4, v4) as 'R4'
)
);
C1 C2 C3 C4 C K V
-- -- -- -- -- -- --
a b c d R1 k1 v1
a b c d R2 k2 v2
e f g h R1 k1 v1
e f g h R2 k2 v2
e f g h R3 k3 v3
e f g h R4 k4 v4
For more on unpivoting, read:
https://blogs.oracle.com/sql/entry/how_to_convert_rows_to#unpivot If you must use SQL*Loader for some reason, then load the CSV into a staging table first. Then you can use unpivot on this to load the data into the real table.