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

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?

CodePudding user response：

You can use a union:

select row_number() over(order by ID, Fileid) as ID,
Fileid,
LanguageID 
from
(select ID, Fileid1 as Fileid, LanguageID1 as LanguageID from table_name
union all select ID, Fileid2 as Fileid, LanguageID2 as LanguageID from table_name
union all select ID, Fileid3 as Fileid, LanguageID3 as LanguageID from table_name) t
where Fileid is not null

Fiddle

CodePudding user response：

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