CODEWITHSUNDEEP
mysqlsetmariadbdistinctconcatenation
rondoe
rondoe

Reputation: 21

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?

Upvotes: 0

Views: 55

Answers (3)

Paul Spiegel
Paul Spiegel

Reputation: 31792

I prefer Barmar's solution, since it's the most readable for me. But this should work too:

select id, concat_ws(',',
  Col_A,
  NULLIF(Col_B, Col_A),               -- NULL if Col_B = Col_A
  NULLIF(NULLIF(Col_C, Col_A), Col_B) -- NULL if Col_C = Col_A or Col_C = Col_B
) as UniqueColumnSet 
from Example

db-fidle

If you are fine with a JSON array as result, you can try this "hack":

select id, json_keys(json_object(Col_A, '', Col_B, '', Col_C, '')) as UniqueColumnSet
from Example;

Result:

| id  | UniqueColumnSet       |
| --- | --------------------- |
| 0   | ["bar", "foo", "qux"] |
| 1   | ["bar", "foo"]        |
| 2   | ["foo", "qux"]        |
| 3   | ["foo"]               |

db-fiddle

Upvotes: 1

Barmar
Barmar

Reputation: 781028

Use UNION to pivot them into the same column, then use GROUP_CONCAT() to concatenate them. UNION will remove duplicates by default.

SELECT id, GROUP_CONCAT(col) AS UniqueColumnSet
FROM (
    SELECT id, col_a AS col
    FROM Example
    UNION
    SELECT id, col_b AS col
    FROM Example
    UNION
    SELECT id, col_c AS col
    FROM Example
) AS x
GROUP BY id

Upvotes: 2

spencer7593
spencer7593

Reputation: 108400

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

Upvotes: 1

Related Questions