Retrieve Distinct concat values from MySQL table

Question

I have an SQL table advert

id       name       cat
11       abc        ab
12       acb        ab, bc
13       abb        bcd
14       abcd       ad
15       acbd       de
16       abbd       ad

On using DISTINCT function I am getting an output like this

Query:

SELECT DISTINCT cat FROM advert;

Output:

ab
ab, bc
bcd
ad
de

WHAT changes do I need to make in my query for output like this

ab
bc
bcd
ad
de

I guess that the best solution would be to assign one category per row. I assume that your `cat` is a varchar — mabe02, Dec 02 '16 at 11:23
I would say wrong table structure. Never put several values into one field. — Seb, Dec 02 '16 at 11:23
What is the maximum number of values that can be in a single column? — David דודו Markovitz, Dec 02 '16 at 11:26
@mabe02 Yes its varchar...the problem is i juz have posted 3 columns it has about 25 coulmns...If i put one value per field then i have to copy these 25 columns twice or may be thrice for three cat values... — Ashish, Dec 02 '16 at 11:30
@Ashish you should probably revise the structure of your tables as @Seb was suggesting. One suggestion could be to consider to create a `cat` table with all the details you don't need to repeat in you other 25 columns and then refer to the `cat_id` — mabe02, Dec 02 '16 at 11:33

David דודו Markovitz · Accepted Answer · 2016-12-02T12:04:20.580

1

select distinct trim(substring_index(substring_index(cat,',',n),',',-1)) as cat

from   t join (select 1 as n union all select 2 union all select 3) r
       on cat like concat('%',repeat(',%',n-1))

edited Dec 02 '16 at 12:04

answered Dec 02 '16 at 11:49

David דודו Markovitz

42,900
6
64
88

can you please help me for another query also (http://stackoverflow.com/questions/40991678/compare-value-with-comma-seperated-string-sql-php) – Ashish Dec 06 '16 at 10:06

score 0 · Answer 2 · answered Dec 02 '16 at 11:37

0

I think you should change your table structure and make it like this.

tblName

id    | name
11       abc        
12       acb       
13       abb       
14       abcd      
15       acbd       
16       abbd

tblCat

id          | name_id |  cat
some ids*       11        ab
                12        ab
                12        bc
                13        bcd
                14        ad
                15        de
                16        ad

In this way you can easily query and manage your data in your tables.

answered Dec 02 '16 at 11:37

Jomar Gregorio

171
1
3
11

I have created table as you recommend....Can you tell me the query to join these two....Thanks... – Ashish Dec 02 '16 at 11:50
select distinct cat from tblCat – Jomar Gregorio Dec 02 '16 at 12:30
1

that query would show your desired result. but if you want to join these two **select distinct b.cat from tblCat as b inner join tblName as a** – Jomar Gregorio Dec 02 '16 at 12:33

score 0 · Answer 3 · answered Dec 02 '16 at 11:39

You should fix your data structure so you are not storing comma-delimited lists in columns. That is the wrong way to store data in a relational database . . . as you can see by the problems for answering this simple question. What you want is a junction table.

Sometimes, we are stuck with other peoples bad designs. You say that there are only two or values, then you can do:

select cat
from ((select substring_index(cat, ', ', 1) as cat
       from advert
      ) union all
      (select substring_index(substring_index(cat, ', ', 2), ', ', -1) as cat
       from advert
       where cat like '%, %'
      ) union all
      (select substring_index(substring_index(cat, ', ', 3), ', ', -1) as cat
       from advert
       where cat like '%, %, %'
      )
     ) c
group by cat;

score 0 · Answer 4 · edited May 23 '17 at 11:52

First... I would create a statement that would turn all the rows into one big massive comma delimited list.

DECLARE @tmp VarChar(max)
SET @tmp = ''
SELECT @tmp = @tmp + ColumnA + ',' FROM TableA

Then use the table valued udf split described by this SO article to turn that massive string back into a table with a distinct clause to ensure that it's unique.

https://stackoverflow.com/a/2837662/261997

SELECT DISTINCT * FROM dbo.Split(',', @tmp)

Full code example:

if object_id('dbo.Split') is not null
    drop function dbo.Split
go
CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )
go
declare @t table (colA varchar(max))
insert @t select '111, 223'
union all select '333'
union all select '444'
union all select '777,999';

select  ltrim(rtrim(s.s)) as colC
from    @t t
cross apply
        dbo.split(',', t.colA) s

Retrieve Distinct concat values from MySQL table

4 Answers4