Search code examples
mysqlsetmariadbdistinctconcatenation

How Can I Concatenate Only Unique Columns in MySQL for Every Row?

How can I concatenate only the unique columns for the columns specified for every row in MySQL?

Table 'Example':

Consider the following table named example:

* -------------------------- *
| Id | Col_A | Col_B | Col_C |
| -------------------------- |
| 0  | foo   | bar   | qux   |
| 1  | foo   | foo   | bar   |
| 2  | foo   | qux   | qux   |
| 3  | foo   | foo   | foo   |
* -------------------------- *

Attempt:

I would like to concatenate only the values in Col_A, Col_B, and Col_C that are unique (only the unique set). This was my attempt:

SELECT Id,
       CONCAT_WS(',', DISTINCT Col_A, Col_B, Col_C) UniqueColumnSet
  FROM Example

Expected Result:

* -------------------- *
| Id | UniqueColumnSet |
| -------------------- |
| 0  | foo,bar,qux     |
| 1  | foo,bar         |
| 2  | foo,qux         |
| 3  | foo             |
* -------------------- *

Error Received:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DISTINCT Col_A, Col_B, Col_C) UniqueColumnSet FROM Example LIMIT 0, 25' at line 2

I am aware that you cannot use DISTINCT like this in CONCAT_WS. What would be the most efficient method to obtain the expected result?

Solution

  • There's a couple of approaches I can think of.

    One would be to use expression in place of Col_B that checks to to see if Col_B matches Col_A, and return a NULL if it does. Same thing for an expression to check Col_C to see if it matches Col_A or Col_B.

    CONCAT_WS ignores NULL values, so something like this:

    SELECT t.id
     , CONCAT_WS(','
           , t.Col_A 
           , IF(t.Col_B IN (t.Col_A), NULL, t.Col_B) 
           , IF(t.Col_C IN (t.Col_A,t.Col_B), NULL, t.Col_C)
       ) AS UniqueColumnSet
  FROM `example` t
 ORDER BY t.id

    Not shown in the example data is a case where the duplicate values are not contiguous, for example

    bar foo bar

    the query above assumes that we'd want to return

    bar,foo