Home » RDBMS Server » Server Utilities » SQL*LDR row split/pivot/mutliple records per row (11.1)
SQL*LDR row split/pivot/mutliple records per row [message #433232] Mon, 30 November 2009 16:20 Go to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
I believe I've reduced my current problem to base principles.

I have raw data that has a fixed number of rows (7 in this case), after the initial ID record there is a record that indicates the number of relevant records and then the remainder of the records are null filled.

RAW DATA EXAMPLE
A	3	a	b	c	null	null
B	4	e	f	g	h	null
C	1	i	null	null	null	null
D	5	j	k	l	m	n


What I want is to load the initial ID (A, B, C, D) multiple times for each valid entry, ideally with a sequence number. (not required).

Ideal output:
A	1	a
A	2	b
A	3	c
B	1	e
B	2	f
B	3	g
B	4	h
C	1	i
D	1	j
D	2	k
D	3	l
D	4	m
D	5	n


Reasonable outputs:
A	a
A	b
A	c
B	e
B	f
B	g
B	h
C	i
D	j
D	k
D	l
D	m
D	n


A	a
A	b
A	c
A	null
A	null
B	e
B	f
B	g
B	h
B	null
C	i
C	null
C	null
C	null
C	null
D	j
D	k
D	l
D	m
D	n


Is this possible? I know I can load as-is and then pivot almost trivially, but I'm interested if there is an efficient & effective way to do this in one step?

[Updated on: Mon, 30 November 2009 16:21]

Report message to a moderator

Re: SQL*LDR row split/pivot/mutliple records per row [message #433236 is a reply to message #433232] Mon, 30 November 2009 20:37 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
-- test.dat:
A	3	a	b	c
B	4	e	f	g	h
C	1	i
D	5	j	k	l	m	n


-- test.ctl:
LOAD DATA
INFILE test.dat
INTO TABLE test WHEN col3 <> ''
FIELDS TERMINATED BY X'09' TRAILING NULLCOLS
  (col1 POSITION (1), filler1 FILLER,
   col3, col2 CONSTANT 1)
INTO TABLE test WHEN col3 <> ''
FIELDS TERMINATED BY X'09' TRAILING NULLCOLS
  (col1 POSITION (1), filler1 FILLER,
   filler2 FILLER,
   col3, col2 CONSTANT 2)
INTO TABLE test WHEN col3 <> ''
FIELDS TERMINATED BY X'09' TRAILING NULLCOLS
  (col1 POSITION (1), filler1 FILLER,
   filler2 FILLER, filler3 FILLER,
   col3, col2 CONSTANT 3)
INTO TABLE test WHEN col3 <> ''
FIELDS TERMINATED BY X'09' TRAILING NULLCOLS
  (col1 POSITION (1), filler1 FILLER,
   filler2 FILLER, filler3 FILLER, filler4 FILLER,
   col3, col2 CONSTANT 4)
INTO TABLE test WHEN col3 <> ''
FIELDS TERMINATED BY X'09' TRAILING NULLCOLS
  (col1 POSITION (1), filler1 FILLER,
   filler2 FILLER, filler3 FILLER, filler4 FILLER, filler5 FILLER,
   col3, col2 CONSTANT 5)


SCOTT@orcl_11g> CREATE TABLE test
  2    (col1  VARCHAR2 (1),
  3  	col2  NUMBER,
  4  	col3  VARCHAR2 (1))
  5  /

Table created.

SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

SCOTT@orcl_11g> COLUMN col1 FORMAT A4
SCOTT@orcl_11g> COLUMN col3 FORMAT A4
SCOTT@orcl_11g> SELECT * FROM test ORDER BY col1, col2, col3
  2  /

COL1       COL2 COL3
---- ---------- ----
A             1 a
A             2 b
A             3 c
B             1 e
B             2 f
B             3 g
B             4 h
C             1 i
D             1 j
D             2 k
D             3 l
D             4 m
D             5 n

13 rows selected.

SCOTT@orcl_11g>

Previous Topic: Oracle-10g /Linux-AS
Next Topic: HOW TO EXPORT JUST CONSTRAINTS & SEQUENCE?
Goto Forum:
  


Current Time: Wed Dec 07 23:57:21 CST 2016

Total time taken to generate the page: 0.11599 seconds