OraFAQ Forum: SQL & PL/SQL » Split column to rows in Oracle Sql

Home » SQL & PL/SQL » SQL & PL/SQL » Split column to rows in Oracle Sql

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

Split column to rows in Oracle Sql [message #640128]

Wed, 22 July 2015 17:03

vspn
Messages: 10
Registered: July 2015

Junior Member

Hi

I have a column which has values separated by comma(,). I need to split this into multiple rows.

Code: [Select all] [Show/ hide]

SELECT NOTE FROM WMS.ORDER_NOTE OR_NT, WMS.ORDERS
WHERE OR_NT.ORDER_ID = ORDERS.ORDER_ID
AND OR_NT.Note_Type  = 'CU'
AND OR_NT.Note_Code  = 'SM'
AND ORDERS.TC_ORDER_ID = '9701187158';

Note
---------
2252998,2252999,2253000

I need to split the above Note into 3 rows as below.

Code: [Select all] [Show/ hide]

Note
------
2252998
2252999
2253000

Please help with the SQL query

Report message to a moderator

Re: Split column to rows in Oracle Sql [message #640129 is a reply to message #640128]

Wed, 22 July 2015 17:47

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

unwilling or incapable to use GOOGLE yourself

https://www.google.com/search?q=Split+column+to+rows+in+Oracle+Sql

Report message to a moderator

Re: Split column to rows in Oracle Sql [message #640132 is a reply to message #640128]

Wed, 22 July 2015 23:43

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

@OP do mention the oracle version you are using, anyways
for 10g & below versions use:-

Code: [Select all] [Show/ hide]

select replace(columnname,',',chr(10)) from table;

for 11g and above use unpivot

Report message to a moderator

Re: Split column to rows in Oracle Sql [message #640134 is a reply to message #640128]

Wed, 22 July 2015 23:49

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

vspn wrote on Thu, 23 July 2015 03:33

I have a column which has values separated by comma(,). I need to split this into multiple rows.

See the examples here http://lalitkumarb.com/2014/12/02/split-comma-delimited-string-into-rows-using-oracle-sql/

But, why do you store data in that fashion? It is not normalized.

Report message to a moderator

Re: Split column to rows in Oracle Sql [message #640137 is a reply to message #640132]

Wed, 22 July 2015 23:56

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

jgjeetu wrote on Thu, 23 July 2015 10:13

for 10g & below versions use:-
Code: [Select all] [Show/ hide]
select replace(columnname,',',chr(10)) from table;

How would that generate 3 rows? OP asked to split into rows:

Quote:

I need to split the above Note into 3 rows as below.

Code: [Select all] [Show/ hide]

SQL> WITH DATA AS(
  2  SELECT '2252998,2252999,2253000' str FROM dual
  3  )
  4  SELECT count(*) FROM(
  5  select replace(str,',',chr(10)) from data);

  COUNT(*)
----------
         1

SQL>

You are just replacing comma with Line feed and not generating rows.

Report message to a moderator

Re: Split column to rows in Oracle Sql [message #640139 is a reply to message #640132]

Thu, 23 July 2015 00:04

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

jgjeetu wrote on Thu, 23 July 2015 10:13

for 11g and above use unpivot

That is not the purpose of UNPIVOT. It will convert the columns into rows, in OP's case the values are not in different columns but in the same column.

Report message to a moderator

Re: Split column to rows in Oracle Sql [message #640141 is a reply to message #640139]

Thu, 23 July 2015 00:27

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

agree @lalit, my mistake n thanks for the correction.

[Updated on: Thu, 23 July 2015 00:28]

Report message to a moderator

Re: Split column to rows in Oracle Sql [message #640290 is a reply to message #640128]

Sun, 26 July 2015 23:42

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

@vspn,

Did you get the solution?

Report message to a moderator

Re: Split column to rows in Oracle Sql [message #640292 is a reply to message #640128]

Mon, 27 July 2015 00:21

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

http://www.orafaq.com/forum/mv/msg/95011/493456/#msg_493456

Report message to a moderator

Re: Split column to rows in Oracle Sql [message #640293 is a reply to message #640292]

Mon, 27 July 2015 00:30

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Michel Cadot wrote on Mon, 27 July 2015 10:51

http://www.orafaq.com/forum/mv/msg/95011/493456/#msg_493456

But OP has denormalized comma separated values in single column and not in different columns.

Report message to a moderator

Re: Split column to rows in Oracle Sql [message #640294 is a reply to message #640293]

Mon, 27 July 2015 01:25

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I took the wrong post of the topic, it is http://www.orafaq.com/forum/mv/msg/95011/569066/#msg_569066.

Report message to a moderator

Previous Topic:	History Tables Search Criteria
Next Topic:	Update to_char(date,'YYYY')

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jun 08 20:31:01 CDT 2025