get all distinct values from a particular column with comma separated values

Question

I am working on to create a sql query. I tried multiple links within stackoverflow and googled it but cant fond a solution. I have the below:

column_name
1,2,3,string1
3,1,string2
4,5,string3
2,4,string1

So i want the below output for this:

1,2,3,4,5,string1,string2,string3,string4

basically i want to get all the unique values from a comma separated column.I know distinct gives different vales compared to each row in the column, but how can we do it when in the above situation.

Note: I am using Oracle 11g database. Kaushik's Answer looks good in SQL Fiddler. but its not working me as i have more than 20k rows. IS there any alternative ?

I removed the incompatible database tags. Please tag only with the database you are really using. — Gordon Linoff, Mar 09 '18 at 15:23
@a_horse_with_no_name Oracle 11g. Will update in the question. — test Account, Mar 09 '18 at 15:35
And is the order of occurrence important? And do you have a unique id column in your table? — Kaushik Nayak, Mar 09 '18 at 16:55

score 3 · Answer 1 · answered Mar 09 '18 at 17:00

Split individual strings into distinct rows using conventional method and then concatenate. Only problem is that the concatenated string shouldn't exceed 4000 characters

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE yourtable
    (column_name varchar2(13))
;

INSERT ALL 
    INTO yourtable (column_name)
         VALUES ('1,2,3,string1')
    INTO yourtable (column_name)
         VALUES ('3,1,string2')
    INTO yourtable (column_name)
         VALUES ('4,5,string3')
    INTO yourtable (column_name)
         VALUES ('2,4,string1')
SELECT * FROM dual
;

Query 1:

select LISTAGG(val,',') WITHIN GROUP ( ORDER BY val ) as final
FROM
(
select distinct  trim(regexp_substr(column_name,'[^,]+', 1, level) ) as val 
  from yourtable
   connect by regexp_substr(column_name, '[^,]+', 1, level) is not null
  ORDER BY val
 )

Results:

|                             FINAL |
|-----------------------------------|
| 1,2,3,4,5,string1,string2,string3 |

Exactly this is what i wanted, but as i have more results(more than 19k results) i think its taking too much time. is there any alternative quicker solution to this? @Kaushik Nayak — test Account, Mar 09 '18 at 20:47
@test Account : A better solution would be to change your table/schema design and store the values as rows with a proper key, by following the rules of normalisation. — Kaushik Nayak, Mar 10 '18 at 01:21

score 1 · Answer 2 · answered Mar 09 '18 at 15:44

You can use regexp_replace to remove the duplicates and concatenate results with listagg

SELECT 
       RTRIM(
         REGEXP_REPLACE(
           (listagg(Num,',') WITHIN GROUP (ORDER BY Num) OVER ()), 
           '([^,]*)(,\1)+($|,)', 
           '\1'),
         ',') Nums 
FROM Table;

score -1 · Answer 3 · answered Mar 09 '18 at 15:56

Answered in this question

create table ##Testdata(SomeID int, OtherId varchar(100), Data varchar(max))
insert ##Testdata select 1, '1,2,3', '1,2,3'
insert ##Testdata select 2, '3,1', '3,1'
insert ##Testdata select 3, '4,5', '4,5'
insert ##Testdata select 4, '2,4', '2,4'

;with tmp(SomeID, OtherID, DataItem, Data) as (
select SomeID, OtherID, LEFT(Data, CHARINDEX(',',Data+',')-1),
    STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from ##Testdata
union all
select SomeID, OtherID, LEFT(Data, CHARINDEX(',',Data+',')-1),
    STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from tmp
where Data > ''
)
select SomeID, OtherID, DataItem
from tmp
order by SomeID
-- OPTION (maxrecursion 0)
-- normally recursion is limited to 100. If you know you have very long
-- strings, uncomment the option

The question states that they are using Oracle 11g. This solution won't work on Oracle. — Boneist, Mar 09 '18 at 16:13
You have just to do a quick search to find how to translate that to oracle: https://community.toadworld.com/platforms/oracle/w/wiki/11655.recursive-common-table-expressions — James, Mar 09 '18 at 16:28

get all distinct values from a particular column with comma separated values

3 Answers3