Hi,
i have Comma separated string value in column of table make_item.
i wanted to show it as List (one single Column). Below is Create and Insert table statement to make it easier. Desire result is also shown.
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
CREATE TABLE MAKE_ITEM
(
ITEM_ID NUMBER,
STYLE_TAGS NVARCHAR2(300),
STYLE_TAGS_O NVARCHAR2(300)
)
/
Insert into MAKE_ITEM
(ITEM_ID,
STYLE_TAGS, STYLE_TAGS_O,
)
Values
(100001,'Full-Sleeve,Half-Sleeve,Pocket,Cuff,Cut,Cross,Slim Fit,Normal Fit,Loose Fit,Fit Knock,Button Design,Button Digit',
'Full-Sleeve,Half-Sleeve,Pocket,Cuff,Cut,Cross,Slim Fit,Normal Fit,Loose Fit,Fit Knock,Button Design,Button Digit'
);
/
Insert into MAKE_ITEM
(ITEM_ID,
STYLE_TAGS, STYLE_TAGS_O,
)
Values
(100002,'Full-Sleeve,Half-Sleeve,Pocket,Cuff,Cut,Cross,Slim Fit,Normal Fit,Loose Fit,Fit Knock,Button Design,Button Digit',
'Full-Sleeve,Half-Sleeve,Pocket,Cuff,Cut,Cross,Slim Fit,Normal Fit,Loose Fit,Fit Knock,Button Design,Button Digit'
);
COMMIT;
/
Result : (not yet done)
Full-Sleeve
Half-Sleeve
Pocket
Cuff
Cut
Cross
Slim Fit
Normal Fit
Loose Fit
Fit Knock
Button Design
Button Digit
/
My Query :
select
level,
item_id,
regexp_substr(style_tags, '[^,]+', 1, (ROW_NUMBER() OVER(PARTITION BY item_id ORDER BY 1))) result1
-- regexp_substr(style_tags_o, '[^,]+', 1, (ROW_NUMBER() OVER(PARTITION BY item_id ORDER BY 1))) result2
--ROW_NUMBER() OVER(PARTITION BY item_id ORDER BY 1),
--(length(regexp_replace(style_tags, '[^,]+')) + 1) cnt
from make_item -- where
where style_tags is not null
--and item_id = 100001
connect by level <= length(regexp_replace(style_tags, '[^,]+')) + 1
Links i have followed :
1)
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4359323800346522748 2)
http://psoug.org/reference/connectby.html 3)
http://steve-lyon.blogspot.in/2013/07/aggregated-lists-decomposing-and.html 4)
http://tkyte.blogspot.in/2007/03/stringing-them-up.html 5)
https://docs.oracle.com/cd/B12037_01/server.101/b10759/functions116.htm
Thanks for a complete test case and showing your working!
Items 100001 and 100002 appear the same though - is this intentional?
There are various ways you can do this. Your approach is close, it's just inefficient. Here's another:
- Generate a row for each item you want to display
- Join this generated table back to the table (cartesian product)
- Use the rownums from the generated table to do the substring
with rws as (
select rownum r from dual
connect by level <= (
select max(regexp_count(style_tags, ',')) from make_item
)
)
select regexp_substr(style_tags, '[^,]+', 1, rws.r) results
from make_item, rws
where item_id = 100001;
RESULTS
--------------
Full-Sleeve
Half-Sleeve
Pocket
Cuff
Cut
Cross
Slim Fit
Normal Fit
Loose Fit
Fit Knock
Button Design
11 rows selected