MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE egsmaster (
  Number_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  category_french VARCHAR(255) DEFAULT '' NOT NULL, 
  category_german VARCHAR(255) DEFAULT '' NOT NULL, 
  category_english VARCHAR(255) DEFAULT '' NOT NULL, 
  category_italian VARCHAR(255) DEFAULT '' NOT NULL);

INSERT IGNORE INTO egsmaster (category_french,
        category_german,
        category_english,
        category_italian) VALUES
('tree','tall', 'hall','itall'),
(NULL,'aaa',NULL,NULL),
(NULL, NULL, 'fff',NULL),
(NULL, NULL,NULL,'yyyy'),
(NULL,'bds', 'bgs','itall'),
('trek','tall',NULL, NULL),                         
(NULL,NULL,NULL,'tr'),
('trq ','sss', 'ggg','tret'),
('12  ','re', 'a12','ass'),
('arro','irrow',NULL,NULL),
('back',NULL,NULL,'bock'),
('sap',NULL,NULL,'Sip'),
(NULL,'Itat', 'itaa',NULL),
(NULL,NULL, 'Iasd','kaka'),
(NULL,NULL,NULL,'sa,p'),
(NULL,NULL,NULL,'lasut');

Records: 16  Duplicates: 0  Warnings: 4


WITH cat_tbl AS (
    SELECT DISTINCT
        category_french,
        category_german,
        category_english,
        category_italian
    FROM
        egsmaster
) SELECT * FROM cat_tbl;


category_french
category_german
category_english
category_italian


tree
tall
hall
itall


aaa





fff





yyyy


bds
bgs
itall

trek
tall






tr

trq
sss
ggg
tret

12
re
a12
ass

arro
irrow



back


bock

sap


Sip


Itat
itaa




Iasd
kaka




sa,p




lasut


/*generating row number*/
WITH cat_tbl AS (
    SELECT DISTINCT
        category_french,
        category_german,
        category_english,
        category_italian
    FROM
        egsmaster
),
  cat_tblrn AS (
SELECT ROW_NUMBER() OVER () AS Rn, cat_tbl.*
FROM cat_tbl) 
SELECT * FROM cat_tblrn;


Rn
category_french
category_german
category_english
category_italian


1
tree
tall
hall
itall

2

aaa



3


fff


4



yyyy

5

bds
bgs
itall

6
trek
tall



7



tr

8
trq
sss
ggg
tret

9
12
re
a12
ass

10
arro
irrow



11
back


bock

12
sap


Sip

13

Itat
itaa


14


Iasd
kaka

15



sa,p

16



lasut


/* with NULL value replacement with IFNULL() - 
in case your empty row data might have both NULL and empty value*/
WITH cat_tbl AS (
    SELECT DISTINCT
        category_french,
        category_german,
        category_english,
        category_italian
    FROM
        egsmaster
),
  cat_tblrn AS (
SELECT ROW_NUMBER() OVER () AS Rn, cat_tbl.*
FROM cat_tbl),
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,'')!='')
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;



category_french
category_german
category_english
category_italian


tree
aaa
fff
yyyy

trek
bds
Iasd
tr

trq
Itat
null
sa,p

12
null
null
lasut

arro
null
null
null

back
null
null
null

sap
null
null
null
category_french	category_german	category_english	category_italian
tree	tall	hall	itall
	aaa
		fff
			yyyy
	bds	bgs	itall
trek	tall
			tr
trq	sss	ggg	tret
12	re	a12	ass
arro	irrow
back			bock
sap			Sip
	Itat	itaa
		Iasd	kaka
			sa,p
			lasut