CODEWITHSUNDEEP
mysqlstring-concatenationdistinct-values
jconnors
jconnors

Reputation: 93

MYSQL: Create list of distinct column value per record

Ticket_Number    Entered_By    Responded_By    Closed_By    Touched_by
----------------------------------------------------------------------
10001            John          NULL            Mike         John, Mike
10002            Sally         Mike            Mike         Sally, Mike
10003            NULL          George          Amber        George, Amber
10004            John          Holly           Timothy      John, Holly, Timothy

My data looks like the above. What I am looking to do is add the last column, Touched_By. Essentially I know I need to concatenate, remove the duplicates and then build a list without duplicated separators. I just don't really know how.

Upvotes: 1

Views: 401

Answers (2)

Jason Heo
Jason Heo

Reputation: 10246

Could you try this? you can test here http://www.sqlfiddle.com/#!2/7a4d2/1

SELECT number, GROUP_CONCAT(by_whom)
FROM (
    SELECT number, entered_by AS by_whom FROM ticket
    UNION
    SELECT number, responsed_by AS by_whom FROM ticket
    UNION
    SELECT number, closed_by AS by_whom FROM ticket
) x
WHERE by_whom IS NOT NULL
GROUP BY number;

+--------+-----------------------+
| number | GROUP_CONCAT(by_whom) |
+--------+-----------------------+
|  10001 | Mike,John             |
|  10002 | Mike,Sally            |
|  10003 | Amber,George          |
|  10004 | John,Timothy,Holly    |
+--------+-----------------------+

Please note that there is max length of GROUP_CONCAT() return value. Default value is enough for you which is 102400. 

mysql> SHOW VARIABLES LIKE 'group_concat_max_len';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| group_concat_max_len | 102400 |
+----------------------+--------+
1 row in set (0.00 sec)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

The function concat_ws() has the nice feature that it skips NULL arguments. That makes it useful for this:

select t.*,
       concat_ws(', ', Entered_By,
                 (case when Responded_By = Entered_By
                       then NULL else Responded_By
                  end),
                 (case when Closed_By = Entered_By or Closed_By = Responded_By
                       then NULL else Closed_By
                  end)
                ) as Resolved_By

Here is a SQL Fiddle.

Upvotes: 0

Related Questions