Fraska Portal

Exploring the WebSphere Commerce world

Oracle, from single line to multiple lines

Posted by on in Tips
  • Font size: Larger Smaller
  • Hits: 1732
  • 18 Comments
  • Subscribe to this entry
  • Print

Sometimes we have to elaborate rows- results of a SELECT- in a way we cannot think to accomplish it with simple SQL code but we can be wrong ... SQL is pretty powerful. Let's take this case.

What we have:

Customer Wishlist
Customer 1 AA11, BB22, FF92
Customer 2 CC43, RR55

What we need:

Customer Wished product
Customer 1 AA11
Customer 1 BB22
Customer 1 FF92
Customer 2 CC43
Customer 2 RR55

 

 

The LIST of products need to be splited- in different rows- according to the token comma ",".  This results can be accomplished using Regular Expression function and Connect By statement. For example: 

SELECT Customer, regexp_replace(Wishlist,'[^,]*') FROM Customers CONNECT BY LEVEL <= length(regexp_replace(Wishlist,'[^,]*'))+1

Rate this blog entry:
0

Comments

Leave your comment

Guest
Guest Sunday, 21 July 2019

Most Popular Post

WebSphere Commerce, the curious life of a front-end catalog request
Core
Rate this blog entry:
5
WebSphere Commerce, the SOLR extension index
Administration
Rate this blog entry:
4
WebSphere Commerce, Data Load and SOLR Delta Index
Data Load
Rate this blog entry:
0

Latest Blogs

WebSphere Commerce, CommandLevelAuthorizationCache
Cache
Rate this blog entry:
0
WebSphere Commerce v8, toolkit exception, ClassNotFound db2
Administration
Rate this blog entry:
1
WebSphere Commerce, ATP migration
Store
Rate this blog entry:
0
WebSphere Commerce, the curious life of a front-end catalog request
Core
Rate this blog entry:
5
WebSphere Commerce, Performance analysis of few European stores
Performance
Rate this blog entry:
0