How to convert linked columns of a table to rows in SQL?

Question

I have a table as follows:

ID | Fileid1 | Fileid2 | Fileid3 | LanguageID1 | LanguageID2 | LanguageID3
---------------------------------------------------------------------------
1  | 123     | 256     |         | a12         | a23         | 
2  | 124     |         |         | a12         |             |

Here the LanguageID1 corresponds to Fileid1, LanguageID2 corresponds to Fileid2 and so on.

I want to convert this into the following:

ID | Field | LangaugeID
1  | 123   | a12
2  | 256   | a23
3  | 124   | a12

I have tried using UNPIVOT on both these fields but it gives a relation between all the columns. What should be the way to do this?

Answer 1

You could try:

select id, Fileid, LanguageID
from (
        select id,fileid1 as Fileid,LanguageID1 as LanguageID 
        from  my_table 
      union all 
        select id,fileid2 as Fileid ,LanguageID2 as LanguageID
        from  my_table 
      union all 
       select id,fileid3 as Fileid ,LanguageID3 as LanguageID
       from  my_table 
       ) as t1
where concat_ws('',Fileid,LanguageID) !=''   

Demo