OraFAQ Forum: SQL & PL/SQL » convert string values into rows

Home » SQL & PL/SQL » SQL & PL/SQL » convert string values into rows (oracle11)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

convert string values into rows [message #633166]

Wed, 11 February 2015 20:38

oracle_Raj
Messages: 3
Registered: February 2015
Location: usa

Junior Member

Hi All, Thank you for your time, I have a table with the below format and need help in getting field converted so that I will have each value as a separate row

EMP_NO  MGR_IR  REPORTING
024067	505445	006910 100085 204227 521244 513713 202731 505445 024067
024097	000819	006910 004155 000527 009159 003709 000819 024097
024129	556191	006910 004694 566317 509762 544201 556191 024129
024176	024893	006910 100085 001131 024893 024176
024182	027370	006910 026605 509788 003204 006076 030193 027370 024182
024207	025593	006910 026605 023837 025232 006606 025593 024207
024254	544795	006910 003057 002623 526778 007974 009574 029026 544795 024254
024258	001861	006910 004155 000527 009159 003709 027594 001861 024258

I would like to get each value in REPORTING as a separate row - looking for sql (not plsql) thanks for your time

[EDITED by LF: applied [code] tags]

[Updated on: Thu, 12 February 2015 00:51] by Moderator

Report message to a moderator

Re: convert string values into rows [message #633167 is a reply to message #633166]

Wed, 11 February 2015 20:41

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Report message to a moderator

Re: convert string values into rows [message #633172 is a reply to message #633167]

Thu, 12 February 2015 00:50

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Here's one option:

SQL> WITH TEST
  2       AS (SELECT '024067' emp_no,
  3                  '505445' mgr_ir,
  4                  '006910 100085 204227 521244 513713 202731 505445 024067'
  5                     reporting
  6             FROM DUAL
  7           UNION
  8           SELECT '024097',
  9                  '000819',
 10                  '006910 004155 000527 009159 003709 000819 024097'
 11             FROM DUAL)
 12  SELECT emp_no,
 13         mgr_ir,
 14         REGEXP_SUBSTR (reporting,
 15                        '\w+',
 16                        1,
 17                        m.COLUMN_VALUE) result
 18    FROM test,
 19         TABLE (
 20            CAST (
 21               MULTISET (
 22                      SELECT LEVEL
 23                        FROM DUAL
 24                  CONNECT BY LEVEL <=
 25                                  LENGTH (reporting)
 26                                - LENGTH (REPLACE (reporting, ' ', ''))
 27                                + 1) AS SYS.odcinumberlist)) m;

EMP_NO MGR_IR RESULT
------ ------ -------------------------------------------------------
024067 505445 006910
024067 505445 100085
024067 505445 204227
024067 505445 521244
024067 505445 513713
024067 505445 202731
024067 505445 505445
024067 505445 024067
024097 000819 006910
024097 000819 004155
024097 000819 000527
024097 000819 009159
024097 000819 003709
024097 000819 000819
024097 000819 024097

15 rows selected.

SQL>

Report message to a moderator

Re: convert string values into rows [message #633206 is a reply to message #633172]

Thu, 12 February 2015 04:35

oracle_Raj
Messages: 3
Registered: February 2015
Location: usa

Junior Member

Thank you, do i need to specify all the values in with test of dual or do we need to provide a sample records .

Providing all values maynot be an option,
Could you please guide me

Report message to a moderator

Re: convert string values into rows [message #633209 is a reply to message #633206]

Thu, 12 February 2015 05:04

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

The WITH clause is just a way of recreating your data without your tables.
You don't need it.

Report message to a moderator

Re: convert string values into rows [message #633222 is a reply to message #633206]

Thu, 12 February 2015 08:01

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Thank you, do i need to specify all the values in with test of dual or do we need to provide a sample records .

You remark comes from the fact you did not read the links BlackSwan provided you.
So here's a reminder.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

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.

Report message to a moderator

Re: convert string values into rows [message #633233 is a reply to message #633222]

Thu, 12 February 2015 10:03

oracle_Raj
Messages: 3
Registered: February 2015
Location: usa

Junior Member

Thank you Michael Cadot
;i will follow the instructions

Report message to a moderator

Previous Topic:	blog image display in utl_mail
Next Topic:	PLS-00597: PLSQL Record and Nested

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 18:57:03 CDT 2024