Home » SQL & PL/SQL » SQL & PL/SQL » row to column (oracle 10g)
row to column [message #580212] Thu, 21 March 2013 07:56 Go to next message
guddu_12
Messages: 172
Registered: April 2012
Location: UK
Senior Member
Hi Gurus,

I have a table where i have description column which free text column, the data in description column is seperated and i want to corvert 1 row data in multiple rows dependeing on the number of words.

eg

id description
78664 Pumps Alarm from CAMS RTU154

In the above example this column has 5 word so i want data in 5 rows like below
78664 Pumps
78664 Alarm
78664 from
78664 CAMS
78664 RTU154

This column data can be varied from 1 to any numbe rof words.

Can you please give any clue to do it easily

Re: row to column [message #580213 is a reply to message #580212] Thu, 21 March 2013 08:10 Go to previous messageGo to next message
Littlefoot
Messages: 19346
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SQL> with
  2  test as
  3    (select '78664 Pumps Alarm from CAMS RTU154' col
  4     from dual
  5    )
  6  select regexp_substr(col, '\w+') c1, regexp_substr(col, '\w+', 1, level + 1) c2
  7  from test
  8  connect by regexp_substr(col, '\w+', 1, level + 1) is not null;

C1    C2
----- ----------------------------------
78664 Pumps
78664 Alarm
78664 from
78664 CAMS
78664 RTU154

SQL>
Re: row to column [message #580214 is a reply to message #580213] Thu, 21 March 2013 08:15 Go to previous messageGo to next message
guddu_12
Messages: 172
Registered: April 2012
Location: UK
Senior Member
Thanks,
Could you please let me know what is level here
Re: row to column [message #580215 is a reply to message #580214] Thu, 21 March 2013 08:20 Go to previous messageGo to next message
Littlefoot
Messages: 19346
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
LEVEL pseudocolumn.
Re: row to column [message #580216 is a reply to message #580212] Thu, 21 March 2013 08:32 Go to previous messageGo to next message
Michel Cadot
Messages: 58638
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topics:

Michel Cadot wrote on Wed, 13 February 2013 16:56
...
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 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.
...


Michel Cadot wrote on Thu, 14 February 2013 00:49
...
Waiting for the test case... (which should take you 1 minute).
...


And feedback to those that help you or ask something; see your previous topics.

Regards
Michel

Re: row to column [message #580222 is a reply to message #580216] Thu, 21 March 2013 08:52 Go to previous messageGo to next message
guddu_12
Messages: 172
Registered: April 2012
Location: UK
Senior Member
I understand that and i would make a habit to send table scripts and test cases. solumn is really fantastic and easy to do other wise i would have done it using loops.

Thanks a lots
Re: row to column [message #580223 is a reply to message #580222] Thu, 21 March 2013 09:03 Go to previous messageGo to next message
Michel Cadot
Messages: 58638
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is based on the principle of row generator.

Regards
Michel
Re: row to column [message #580833 is a reply to message #580223] Thu, 28 March 2013 08:31 Go to previous messageGo to next message
guddu_12
Messages: 172
Registered: April 2012
Location: UK
Senior Member
Hi Michel,

How can i break the level.

say for instance in the example above we have one row select '78664 Pumps Alarm from CAMS RTU154' col from dual. if we have another row like select '78665 Pumps Alarm from CAMS RTU154' col from dual
.
then for each number (78664,78665 ) level should generate rows seperately to give correct result



Re: row to column [message #580838 is a reply to message #580833] Thu, 28 March 2013 09:05 Go to previous messageGo to next message
Michel Cadot
Messages: 58638
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See http://www.orafaq.com/forum/mv/msg/186789/580216/102589/#msg_580216.

Regards
Michel
Re: row to column [message #580851 is a reply to message #580212] Thu, 28 March 2013 11:15 Go to previous messageGo to next message
guddu_12
Messages: 172
Registered: April 2012
Location: UK
Senior Member
Hi

table and data is below

create table inc_desc (incdnt_no number ,fault_desc varchar2(4000))

insert into inc_desc values(78664,' Pumps Alarm from CAMS RTU154');

insert into inc_desc values(78664,' Ramp Alarm inc desc');


the expected output is
78664 Pumps
78664 Alarm
78664 from
78664 CAMS
78664 RTU154
78664 Ramp
78664 Alarm
78664 inc
78664 desc

the below solution work good for 1 row but worng with more than one row and the only problem is to break the level at incdnt_no.

with
test as
  (select incdnt_no || fault_desc  col
     from inc_desc 
    )
  select regexp_substr(col, '\w+') c1, regexp_substr(col, '\w+', 1, level + 1) c2
  from test
  connect by regexp_substr(col, '\w+', 1, level + 1) is not null ;


Hope it is understandable.
Re: row to column [message #580852 is a reply to message #580851] Thu, 28 March 2013 11:17 Go to previous messageGo to next message
guddu_12
Messages: 172
Registered: April 2012
Location: UK
Senior Member
data is as below
insert into inc_desc values(78664,' Pumps Alarm from CAMS RTU154');

insert into inc_desc values(78665,' Ramp Alarm inc desc');



the expected output is
78664 Pumps
78664 Alarm
78664 from
78664 CAMS
78664 RTU154
78665 Ramp
78665 Alarm
78665 inc
78665 desc
Re: row to column [message #580853 is a reply to message #580852] Thu, 28 March 2013 11:38 Go to previous message
Michel Cadot
Messages: 58638
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't read the I posted abut row generator.
There is EXACTLY the same case than you with 3 different solutions (near the bottom).

Regards
Michel
Previous Topic: Read only the input string from BLOB datatype column
Next Topic: Parsing BLOB Contents
Goto Forum:
  


Current Time: Fri Aug 01 00:13:22 CDT 2014

Total time taken to generate the page: 0.06439 seconds