PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Monday, August 29, 2022

[FIXED] How to split single rows into multiple rows depending on whether one field contains comma separated string in oracle sql?

 August 29, 2022     csv, oracle, split     No comments   

Issue

I have created a function get_depatment_names which returns comma separated values in the single Return Value. The output of function is : Sales,Retail,Electronic.

Another function get_location_names returns the same way for the location. Output: West York,Shire,Lancas

Now, these two functions , I'm calling in my select statement along with other columns.

SELECT dept_id,
       date,
       get_department_names dept_name,
       get_location_names location
       status
FROM   department
WHERE dept_id = 1;

Output is displayed as below:

dept_id ---date ----dept_name -------location----- status

1 ---01/01/2018 --- Sales,Retail,Electronic ---West York,Shire,Lancas-- Active

Expected output:

1--01/01/2018 --- Sales --- West York-- Active

1--01/01/2018 --- Retail --- Shire -- Active

1--01/01/2018 --- Electronic ---Lancas --Active

I tried using the regexp_sub with connect in the select stmt as below but is giving an error of "single row subquery returns more than one row".

SELECT dept_id,
       date,
       (select regexp_substr(get_department_names(id),'[^,]+',1,level) from dual
        connect by regexp_substr(get_department_names(id),'[^,]+',1,level) is not null)  dept_name,
       (select regexp_substr(get_location_names (id),'[^,]+',1,level) from dual
        connect by regexp_substr(get_location_names(id),'[^,]+',1,level) is not null) location
       status
FROM   department
WHERE dept_id = 1;

Please let me know how to correct this.


Solution

That would be something like this (lines #1 - 4 represent sample data; query you need begins at line #5):

SQL> with department (dept_id, datum, dept_name, location, status) as
  2    (select 1, date '2018-01-01', 'sales,retail,electronic',
  3      'West York,Shire,Lancas', 'active' from dual
  4    )
  5  select dept_id,
  6    datum,
  7    regexp_substr(dept_name, '[^,]+', 1, level) dept_name,
  8    regexp_substr(location , '[^,]+', 1, level) location,
  9    status
 10  from department
 11  where dept_id = 1
 12  connect by level <= regexp_count(dept_name, ',') + 1;

   DEPT_ID DATUM      DEPT_NAME       LOCATION        STATUS
---------- ---------- --------------- --------------- ------
         1 01/01/2018 sales           West York       active
         1 01/01/2018 retail          Shire           active
         1 01/01/2018 electronic      Lancas          active

SQL>


Answered By - Littlefoot
Answer Checked By - Katrina (PHPFixing Volunteer)
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing