Mark Helmstetter Mark Helmstetter - 4 months ago 20
SQL Question

Concatenate references of duplicate values in MySQL

I have a table (chapter) that contains 5 columns for officers in an organization: ID (key), president, vice_president, secretary, treasurer. For each office there is the value of a reference number to an individual.

For some IDs, the same value is listed for more than one of the 4 offices. You can see a basic example of my data structure below:

ID president vice_president secretary treasurer
105 1051456 1051456 1051466 1051460
106 1060923 1060937 1060944 1060944
108 1081030 1081027 1081032 1081017
110 1100498 1100491 1100485 1100485


I have also posted the same at http://sqlfiddle.com/#!9/57df1

My goal is to identify when a value is in more than one field and to SELECT that value as well as a concatenated list of all of the column titles in which it is found. For example from the supplied sample dataset, I would ideally like to return the following:

member offices
1051456 president, vice_president
1060944 secretary, treasurer
1100485 secretary, treasurer


I have found a few other examples that are similar, but nothing seems work towards what I am looking to do. I'm a novice but can piece things together from examples fairly well. I was also thinking that there might be an easier way by joining with the information_schema database as that is how I have pulled column titles in the past. It doesn't seem that this should as difficult as it is, and hopefully I am missing an easy and obvious solution. My full dataset is rather large and I would prefer to avoid any intensive sub-queries for the sake of performance. My SQL format is MySQL 5.5.

Any help or guidance would be greatly appreciated!

Answer

One method uses union all to unpivot the data and then re-aggregates:

select member, group_concat(office)
from ((select id, president as member, 'president' as office from t) union all
      (select id, vice_president, 'vice_president' as office from t) union all
      (select id, secretary, 'secretary' as office from t) union all
      (select id, treasurer, 'treasurer' as office from t)
     ) t
group by member
having count(distinct office) > 1;

If you want to control the order of the values, then add a priority:

select member, group_concat(office order by priority) as offices
from ((select id, president as member, 'president' as office, 1 as priority from t) union all
      (select id, vice_president, 'vice_president' as office, 2 from t) union all
      (select id, secretary, 'secretary' as office, 3 from t) union all
      (select id, treasurer, 'treasurer' as office, 4 from t)
     ) t
group by member
having count(distinct office) > 1;