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 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