how to Select distinct from all columns - display the data to the first column where it occurs

Question

I have a table named egsmaster mysql workbench version : MySQL Workbench was 8.0.27

so i have this columns named number_id, product, french, german, english, italian this language are the category for the product so i want to get a return list of distinct record from all the columns,

first get the distinct record from french, then german, english and italian so i did that

here is my query

SELECT
    COALESCE(category_french, '') AS french,
    COALESCE(category_german, '') AS german,
    COALESCE(category_english, '') AS english,
    COALESCE(category_italian, '') AS italian
FROM (
    SELECT DISTINCT
        category_french,
        category_german,
        category_english,
        category_italian
    FROM
        egsmaster
) AS t
WHERE
    COALESCE(category_french, '') <> ''
    OR COALESCE(category_german, '') <> ''
    OR COALESCE(category_english, '') <> ''
    OR COALESCE(category_italian, '') <> ''
    AND (category_french IS NOT NULL
    OR category_german IS NOT NULL
    OR category_english IS NOT NULL
    OR category_italian IS NOT NULL);

if i run this query i will get a result set like this

french | german | english | italian 
tree   |  tall  |  hall   |  itall
       |  aaa   |         |
       |        |  fff    |
       |        |         | yyyy
       | bds    | bgs     | itall
trek   | tall   |         |             
       |        |         | tr
trq    |  sss   |   ggg   | tret
12     |  re    |   a12   | ass
arrow  |  irrow |         |
back   |        |         | bock              
sap    |        |         | Sip
       | Itat   |  itaa   |
       |        |  Iasd   | kaka
       |        |         | sa,p
       |        |         | lasut 

but what i want is to have a clean result set so that if i export it in excel it will show like this

french | german | english | italian |
tree     aaa      fff       yyyy
trek     bds      lasd      tr
trq      itat               sa,p
12                          lasut
arrow
back
sap 

in a row if it has multiple columns that contains a data the first column where it occurs must be the only that needs to return

regardless with the id what i only need to do is get the distinct data from all of the language column

Answer 1

Here's an idea however, this only works on MySQL v8+.

I'll be using Common Table Expression (cte) and ROW_NUMBER() as main function and use IFNULL() as optional function - only if your data consists of both NULL and '' for empty values.

First, we'll be using your example query as base and wrap that in the first cte like this:

/*1st part*/
WITH cat_tbl AS (
    SELECT DISTINCT
        category_french,
        category_german,
        category_english,
        category_italian
    FROM
        egsmaster
)

Now we use cat_tbl in our second cte to generate ROW_NUMBER():

/*1st part*/..,
/*2nd part*/
  cat_tblrn AS (
SELECT ROW_NUMBER() OVER () AS Rn, cat_tbl.*
FROM cat_tbl)

Now we have the row number as reference for the next row number that we'll generate separately for each language column. According to your data sample, category_french is the first column to be list out. Here's a cte for it:

/*1st part*/..,
/*2nd part*/..,
/*3rd part - 1*/
cat_fr AS (
  SELECT ROW_NUMBER() OVER (ORDER BY Rn) AS FrRn,
         category_french
  FROM cat_tblrn
  WHERE IFNULL(category_french,'')!=''),

cat_fr is the cte name with condition where category_french column value is not empty (or NULL).

Note: As I mentioned, if you're not sure empty data value is either NULL or '', then you can use IFNULL() function like in the query. However, if empty values are always NULL then you can change the part IFNULL(column_name,'')!='' to column_name IS NOT NULL. Or if empty column always '' then simply column_name != ''.

The next cte is for category_german:

/*1st part*/..,
/*2nd part*/..,
/*3rd part - 2*/
cat_fr AS (
  SELECT ROW_NUMBER() OVER (ORDER BY Rn) AS FrRn,
         category_french
  FROM cat_tblrn
  WHERE IFNULL(category_french,'')!=''),
cat_gr AS (
  SELECT ROW_NUMBER() OVER (ORDER BY Rn) AS GrRn,
         category_german
  FROM cat_tblrn
  WHERE IFNULL(category_french,'')='' AND IFNULL(category_german,'')!='' )

The difference between this cat_gr and cat_fr cte are obviously the column in SELECT will be category_german with an additional IFNULL(category_french,'')='' in WHERE to indicate that cat_gr should only return value when category_french is empty. And the rest of the cte will have the same WHERE with additional checking of each previous column value that will end up something like this:

/*1st part*/..,
/*2nd part*/..,
/*3rd part - full*/
cat_fr AS (
  SELECT ROW_NUMBER() OVER (ORDER BY Rn) AS FrRn,
         category_french
  FROM cat_tblrn
  WHERE IFNULL(category_french,'')!=''),
cat_gr AS (
  SELECT ROW_NUMBER() OVER (ORDER BY Rn) AS GrRn,
         category_german
  FROM cat_tblrn
  WHERE IFNULL(category_french,'')='' AND IFNULL(category_german,'')!='' ),
cat_en AS (
  SELECT ROW_NUMBER() OVER (ORDER BY Rn) AS EnRn,
         category_english
  FROM cat_tblrn
  WHERE IFNULL(category_french,'')='' AND IFNULL(category_german,'')='' 
    AND IFNULL(category_english,'')!=''),
cat_ita AS (
  SELECT ROW_NUMBER() OVER (ORDER BY Rn) AS ItaRn,
         category_italian
  FROM cat_tblrn
  WHERE IFNULL(category_french,'')='' AND IFNULL(category_german,'')='' 
    AND IFNULL(category_english,'')='' AND IFNULL(category_italian,'')!='')

Now that we have all the cte prepared, we can simply use LEFT JOIN to get the desired result:

/*1st part*/..,
/*2nd part*/..,
/*3rd part*/..,
/*final part*/
SELECT category_french, 
       category_german,
       category_english,
       category_italian
  FROM cat_fr 
  LEFT JOIN cat_gr ON FrRn=GrRn
  LEFT JOIN cat_en ON FrRn=EnRn
  LEFT JOIN cat_ita ON FrRn=ItaRn;

Result:

category_french	category_german	category_english	category_italian
tree	aaa	fff	yyyy
trek	bds	Iasd	tr
trq	Itat		sa,p
12			lasut
arro
back
sap

Get the full query in this demo fiddle