How to Select DISTINCT CONTACT of some Columns in Mysql

Question

I have a join query that returns data like this :

Column A	Column B	Column C	Column D	Column E	Column F	Column G	CONCAT_WS(',',D,E,F,G)
id1	id2	id3	14	15	14	16	14,15,14,16
--------	--------	--------	--------	--------	--------	--------	----------------------
id5	id6	id7	14	15	17	16	14,15,17,16

I want the last column to only return unique values found in each record. For example, for the first record I want the last column to have values: (14,15,16) instead of 14,15,14,16.

Answer 1

sql is row based rather than column based so there is no function which can operate on columns in the way you want.

If you have a unique row identifier you could UNPIVOT using UNION which comes with an implicit distinct , group_concat and join for example

DROP TABLE IF EXISTS T;

create table t
(id int , col1 int,col2 int,col3 int);

insert into t values
(1,1,2,3),(2,1,1,2),(3,3,3,3);

with cte as
(select id,col1 col from t
union
select id,col2 from t
union
select id,col3 from t
)
select *,gc
from t
join (select id,group_concat(col) gc from cte group by id) s on s.id = t.id
order by t.id;

+------+------+------+------+------+-------+-------+
| id   | col1 | col2 | col3 | id   | gc    | gc    |
+------+------+------+------+------+-------+-------+
|    1 |    1 |    2 |    3 |    1 | 1,2,3 | 1,2,3 |
|    2 |    1 |    1 |    2 |    2 | 2,1   | 2,1   |
|    3 |    3 |    3 |    3 |    3 | 3     | 3     |
+------+------+------+------+------+-------+-------+
3 rows in set (0.023 sec)

if you don't have a unique row identifier you might get away with assigning one but there's no guarantee the rows output will be in the same order as the rows input.

with cte as
(select id,col1 col from t
union
select id,col2 from t
union
select id,col3 from t
),
cte1 as
(select id,row_number() over () rn from t)

select *,gc
from cte1 t
join (select id,group_concat(col) gc from cte group by id) s on s.id = t.id
order by t.id;

Both solution look poor performers but that's what happens with poorly built data.