MYSQL delete duplicates values from a select concat-CodePudding

Hello i created a query for get the titles of columns (for import in vba)

SELECT CONCAT( GROUP_CONCAT(column_name)) as columns
FROM information_schema.columns
WHERE table_name = 'trans_int_prix_gen'
   OR table_name = 'trans_int_prix_module'
   OR table_name = 'matrice_sigma' 
ORDER BY column_name ASC;

I want improve the request by deleting values which are equal to 2 fields in this request. Or delete duplicated values of thoses 2 fields thanks

the result look like this

SIGMA, UV, TOP, EAN, CM, SIGMA, UV, 1, 2, 3 ,4 ,5, SIGMA, UV, 3, 4, 5

I want display in the result SIGMA, UV only one time

CodePudding user response：

Use GROUP_CONCAT with DISTINCT:

SELECT GROUP_CONCAT(DISTINCT column_name) AS columns
FROM information_schema.columns
WHERE table_name = 'trans_int_prix_gen'    OR
      table_name = 'trans_int_prix_module' OR
      table_name = 'matrice_sigma';

Note that the ORDER BY clause in your current query doesn't do anything, since there will always be just a single record in the result set. What you wanted to do is this:

SELECT GROUP_CONCAT(DISTINCT column_name ORDER BY column_name) AS columns
...

But by default column_name will be used for sorting the CSV string, so we don't need to explicitly use an ORDER BY clause with GROUP_CONCAT in this case.