Comma separated values from multi columns as rows

Question

I have a table tbl_commaseperate with columns ID, MONNAME, IP and POLICY whose values are:

ID |  MONNAME | IP    | POLICY
-------------------------------
X  |  NOV     | 1,2,3 | 4,5,6,7

where IP and POLICY have comma separated values.

My desired result looks like as below:

ID | MONNAME | IP    | POLICY
------------------------------
X  | NOV     |  1    |  4
X  | NOV     |  2    |  5
X  | NOV     |  3    |  6
X  | NOV     |  null |  7

So you are trying to get away from that horrible table design to a better table? That's a good idea. Your best bet may be a pipelined PL/SQL function that reads the records given, loops through your arrays and puts out the desired rows. As you haven't asked any question; does this answer your question(s)? — Thorsten Kettner, Nov 04 '16 at 09:00
See here: http://stackoverflow.com/questions/18770581/oracle-split-multiple-comma-separated-values-in-oracle-table-to-multiple-rows or here: http://stackoverflow.com/questions/28677070/split-function-in-oracle-to-comma-separated-values-with-automatic-sequence or here: http://stackoverflow.com/questions/28956226/splitting-comma-separated-list-into-a-temp-table — , Nov 04 '16 at 13:00

score 0 · Answer 1 · answered Nov 04 '16 at 07:53

Please try this one.

Create a function to split comma separated string.

CREATE FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(max) -- List of delimited items
  , @sDelimiter VARCHAR(max) = ',' -- delimiter that separates items
) RETURNS @List TABLE (SplitValue VARCHAR(max))

BEGIN
DECLARE @sItem VARCHAR(max)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
    BEGIN
        SELECT
          @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
          @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

         IF LEN(@sItem) > 0
            INSERT INTO @List SELECT @sItem
    END

IF LEN(@sInputList) > 0
    INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END

And then write your query like this.

select * from (
    select SplitValue,ROW_NUMBER() over(order by SplitValue) rowNo FROM dbo.fnSplit('1,2,3',',') 
) as a 
full join (
    select SplitValue,ROW_NUMBER() over(order by SplitValue) rowNo FROM dbo.fnSplit('4,5,6,7',',')
) as b on a.rowNo=b.rowNo

replace your column in hardcore string and. Note: You can also write with query instead of function.

score 0 · Answer 2 · edited Nov 04 '16 at 12:58

0

CREATE TABLE #Table ( ID VARCHAR(100),MONNAME VARCHAR(100), IP VARCHAR(100) , POLICY VARCHAR(100))
INSERT INTO #Table (ID ,MONNAME, IP, POLICY)SELECT 'X','NOV',1,2,3,4','4,5,6,7'
;WITH _CTE ( _id ,_MONNAME , _IP   , _POLICY , _RemIP , _RemPOLICY) AS (
SELECT ID ,MONNAME , SUBSTRING(IP,0,CHARINDEX(',',IP)),       SUBSTRING(POLICY,0,CHARINDEX(',',POLICY)),
SUBSTRING(IP,CHARINDEX(',',IP)+1,LEN(IP)),
SUBSTRING(POLICY,CHARINDEX(',',POLICY)+1,LEN(POLICY))
FROM #Table
UNION ALL
SELECT _id ,_MONNAME , CASE WHEN CHARINDEX(',',_RemIP) = 0 THEN _RemIP ELSE
SUBSTRING(_RemIP,0,CHARINDEX(',',_RemIP)) END, CASE WHEN CHARINDEX(',',_RemPOLICY) = 0 THEN _RemPOLICY ELSE SUBSTRING(_RemPOLICY,0,CHARINDEX(',',_RemPOLICY)) END,
CASE WHEN CHARINDEX(',',_RemIP) = 0 THEN '' ELSE SUBSTRING(_RemIP,CHARINDEX(',',_RemIP)+1,LEN(_RemIP)) END,
CASE WHEN CHARINDEX(',',_RemPOLICY) = 0 THEN '' ELSE SUBSTRING(_RemPOLICY,CHARINDEX(',',_RemPOLICY)+1,LEN(_RemPOLICY)) END
FROM _CTE WHERE _RemIP <> '' OR _RemPOLICY <> ''
)
SELECT _id id,_MONNAME MONNAME, _IP IP , _POLICY POLICY 
FROM _CTE

edited Nov 04 '16 at 12:58

answered Nov 04 '16 at 09:37

Mansoor

4,061
1
17
27

1

This is invalid for Oracle – Nov 04 '16 at 12:58
He didn't specified tags – Mansoor Nov 04 '16 at 13:00
The question was tagged with `oracle11g` right from the start. – Nov 04 '16 at 13:01
SQL also tagged – Mansoor Nov 04 '16 at 13:02
The tag `sql` refers to the **query** language SQL, it does not refer to any specific DBMS - and your answer isn't (standard) SQL either. – Nov 04 '16 at 13:04

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

Oracle

with        r (id,monname,ip,policy,n,max_tokens)
            as
            (
                select      id,monname,ip,policy
                           ,1
                           ,greatest (nvl(regexp_count(ip,'[^,]+'),0),nvl(regexp_count(policy,'[^,]+'),0))            
                from        tbl_commaseperate
                
                union all
                
                select      id,monname,ip,policy
                           ,n+1
                           ,max_tokens                  
                from        r                
                where       n < max_tokens
                )
                
select      r.id
           ,r.monname
           ,regexp_substr (ip    ,'[^,]+',1,n)  as ip
           ,regexp_substr (policy,'[^,]+',1,n)  as policy

from        r
;

score 0 · Accepted Answer · answered Nov 06 '16 at 01:23

The output is in no particular order. Also, in your desired output you don't seem to care which pair was first, which second etc. (but that can be preserved in the query, if needed).

I added a row for more testing; I have NULL for policy - which is how I realized I needed the coalesce() around the regexp_count.

with
     inputs ( id ,monname, ip , policy ) as (
       select 'X', 'NOV', '1,2,3' , '4,5,6,7' from dual union all
       select 'X', 'DEC', '6,3,8', null      from dual
     )
-- end of test data; solution (SQL query) begins below this line
select id, monname,
       regexp_substr(ip    , '[^,]+', 1, level) as ip,
       regexp_substr(policy, '[^,]+', 1, level) as policy
from   inputs
connect by level <= 1 + greatest( coalesce(regexp_count(ip    , ','), 0),
                                  coalesce(regexp_count(policy, ','), 0)  )
    and prior id = id
    and prior monname = monname
    and prior sys_guid() is not null
;

ID MONNAME IP    POLICY
-- ------- ----- -------
X  DEC     6            
X  DEC     3            
X  DEC     8            
X  NOV     1     4      
X  NOV     2     5      
X  NOV     3     6      
X  NOV           7      

 7 rows selected

Comma separated values from multi columns as rows

4 Answers4

Oracle