second attempt at this question with more detail. I am attempting to group the distinct values of multiple columns together for objects of the same name. I can use GROUP_CONCAT on each of the 'Type' columns, but I can't merge them together to get a distinct set of values for each name
Here is a sample of my data:
+----------+-------+-------+-------+
| Company | Type1 | Type2 | Type3 |
+----------+-------+-------+-------+
| Generic | 1 | NULL | 3 |
+----------+-------+-------+-------+
| Generic | NULL | 2 | 2 |
+----------+-------+-------+-------+
| Generic | 3 | 2 | NULL |
+----------+-------+-------+-------+
| Generic2 | 1 | NULL | NULL |
+----------+-------+-------+-------+
| Generic2 | NULL | 2 | 2 |
+----------+-------+-------+-------+
| Generic2 | 1 | 2 | NULL |
+----------+-------+-------+-------+
And here is the basic query I have to come up with that does NOT work as desired:
SELECT s.company, CONCAT(GROUP_CONCAT(DISTINCT s.type1),',',GROUP_CONCAT(DISTINCT s.type2),',',GROUP_CONCAT(DISTINCT s.type3)) AS GROUPED
FROM sample s
GROUP BY s.company
The above query returns:
+----------+-----------+
| Company | GROUPED |
+----------+-----------+
| Generic | 1,3,2,3,2 |
+----------+-----------+
| Generic2 | 1,2,2 |
+----------+-----------+
What I need it to return is a grouping of the groups with distinct values only:
+----------+---------+
| Company | GROUPED |
+----------+---------+
| Generic | 1,2,3 |
+----------+---------+
| Generic2 | 1,2 |
+----------+---------+
Is this possible?
One option is to unpivot the columns to rows before grouping. In MySQL, you can do this with union all:
select company, group_concat(distinct typex order by typex) res
from (
select company, type1 typex from mytable
union all select company, type2 from mytable
union all select company, type3 from mytable
) t
group by company
company | res :------- | :---- Generic | 1,2,3 Generic2 | 1,2