CODEWITHSUNDEEP
mysqlduplicatesconcatenation
mivk
mivk

Reputation: 14834

mysql concat_ws without duplicates

I am trying to concatenate a few fields into a single one, but only keep unique values in the resulting string.

Example:

title_orig | title_fr   | title_de                | title_it
---------------------------------------------------------------------
KANDAHAR   | KANDAHAR   | REISE NACH KANDAHAR     | VIAGGO A KANDAHAR
SCREAM 2   | SCREAM 2   | SCREAM 2                | SCREAM 2

With CONCAT_WS(', ', title_orig, title_fr, title_de, title_it) AS titles I would get

titles
------------------------------------------------------------
KANDAHAR, KANDAHAR, REISE NACH KANDAHAR, VIAGGO A KANDAHAR
SCREAM 2, SCREAM 2, SCREAM 2, SCREAM 2

But I would like to get

titles
------------------------------------------------
KANDAHAR, REISE NACH KANDAHAR, VIAGGO A KANDAHAR
SCREAM 2

In PostgreSQL, I used a plperl stored procedure (%seen=(); return join(", ", grep {$_ && ! $seen{$_}++} @_).

How could I get rid of the duplicates in MySQL?

Upvotes: 3

Views: 2697

Answers (2)

mivk
mivk

Reputation: 14834

There is an alternative solution using group_concat(), but it is also quite cumbersome:

SELECT id, group_concat(
  DISTINCT titles
  ORDER BY titles
  SEPARATOR ', ')
FROM (
  SELECT id, title_orig AS titles FROM test
  UNION ALL SELECT id, title_fr FROM test 
  UNION ALL SELECT id, title_de FROM test 
  UNION ALL SELECT id, title_it FROM test
) AS tmp
GROUP BY id;

Upvotes: 0

Eugen Rieck
Eugen Rieck

Reputation: 65274

Needs a bit of work:

SELECT 
  CONCAT(
    title_orig,
    IF(title_fr=title_orig,'',CONCAT(', ',title_fr)),
    IF(title_de=title_orig OR title_de=title_fr,'',CONCAT(', ',title_de)),
    IF(title_it=title_orig OR title_it=title_fr OR title_it=title_de,'',CONCAT(', ',title_it))
  ) AS title_concat
FROM
  ...

Upvotes: 2

Related Questions