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
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
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.
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
;
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
