Home » SQL & PL/SQL » SQL & PL/SQL » Combining rows in data file based on values in control file
Combining rows in data file based on values in control file [message #594510] Thu, 29 August 2013 06:12 Go to next message
sivapodila
Messages: 1
Registered: October 2011
Junior Member
I have to load data file into a table. And the requirement is as below:

Input Data:

1234|20130815|20130822|This is a test, this is the the part
3456|20130823|20130809|This is a test
3456|20130823|20130809|This is a test
3456|20130823|20130809|This is a test
3456|20130823|20130809|Siva 1234

The data should be inserted only in two rows as below:
When Value in first 3 fields is same, 4th field should be appended to the existing value in table.

1234|20130815|20130822|This is a test, this is the the part
3456|20130823|20130809|This is a testThis is a testThis is a testSiva 1234

Please help me out if we can do this using any parameters in ctl file loading.
Thank you in advance

[Updated on: Thu, 29 August 2013 06:23]

Report message to a moderator

Re: Combining rows in data file based on values in control file [message #594511 is a reply to message #594510] Thu, 29 August 2013 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

What you ask is easy to be done with an external table and the standard to concatenate group of rows for your version.

Regards
Michel
Re: Combining rows in data file based on values in control file [message #597060 is a reply to message #594510] Mon, 30 September 2013 12:19 Go to previous message
Barbara Boehmer
Messages: 7973
Registered: November 2002
Location: California, USA
Senior Member
I would use SQL*Loader to load it into a staging table as is, then use a SQL insert statement with the LISTAGG function to insert it the way you want it into the target table.
Previous Topic: ".." Character coming instead of "." while creating an excel spreadsheet from Oracle 10g
Next Topic: NTILE Issue
Goto Forum:
  


Current Time: Sun Aug 31 05:56:26 CDT 2014

Total time taken to generate the page: 0.25163 seconds