I have data as below

I would like to duplicate the following columns: first name and last name where the profession(s) column doesnt contain N/A value.

id  firstname   lastname    profession 1    profession 2    profession 3    profession 4    profession 5  
1   firstname1  lastname1   nurse           physician       n/a             n/a             n/a 
2   firstname2  lastname2   physician       nurse           n/a             n/a             n/a 
3   firstname3  lastname3   n/a             n/a             n/a             n/a             n/a 
4   firstname4  lastname4   nurse           n/a             n/a             n/a             n/a 
5   firstname5  lastname5   physician       n/a             n/a             n/a             n/a
6   firstname6  lastname6   nurse           midwife         physician       dentist         n/a 
7   firstname7  lastname7   physician       midwife         dentist         n/a             n/a

here is the sample output i needed.

firstname1  lastname1   nurse
firstname1  lastname1   physician
firstname2  lastname2   physician
firstname2  lastname2   nurse
firstname3  lastname3   n/a
firstname4  lastname4   nurse
firstname5  lastname5   physician
firstname6  lastname6   nurse
firstname6  lastname6   midwife
firstname6  lastname6   physician
firstname6  lastname6   dentist
firstname7  lastname7   physician
firstname7  lastname7   midwife
firstname7  lastname7   dentist

Thank you so much.

CodePudding user response：

The way we unpivot in MySQL is to use union all.

select firstname, lastname, profession_1 as profession
from t
where  profession_1 <> 'n/a'
  
union all
  
select firstname, lastname, profession_2 
from t  
where  profession_2 <> 'n/a'

union all
  
select firstname, lastname, profession_3 
from t  
where  profession_3 <> 'n/a'

union all
  
select firstname, lastname, profession_4 
from t
where  profession_4 <> 'n/a'

union all
  
select firstname, lastname, profession_5 
from t
where  profession_5 <> 'n/a'

order by firstname, lastname

Fiddle