从指向同一个表的外键列生成多行

I have a data row with multiple fk pointing to the same table. How can I generate this:

我有一个数据行，多个fk指向同一个表。我怎样才能生成这个：

table 1

表格1

 id | name | fk1 | fk2 | fk3
 1    test   EA    US    NULL
 2    test2  Null  UK    US

table 2

表2

id | details
EA   East Asia
US   United States
UK   United Kingdom

I want to generate something like this

我想生成这样的东西

id | name | details
1    test   East Asia
1    test   United States
2    test2  United Kingdom
2    test2  United States

I've been looking around but probably I'm typing the wrong search keyword or phrase.

我一直在环顾四周，但可能是我输错了搜索关键字或短语。

thanks

谢谢

this is what I did

这就是我所做的

select t1.id,t1.name,t2.details from table1 t1 
left join table2 t2 on t2.id = t1.fk1
union
(select t1.id,t1.name,t2.details from table1 t1 
left join table2 t2 on t2.id = t1.fk2
)
union
select t1.id,t1.name,t2.details from table1 t1 
left join table2 t2 on t2.id = t1.fk3

but this table generates row with null

但是这个表生成了一个null行

1 个解决方案

#1

Use UNPIVOT to get each of your fk columns as a separate row.

使用UNPIVOT将每个fk列作为单独的行。

select u.id, u.name, t2.details
from table1 t1
unpivot(
    region for regions in (fk1, fk2, fk3)
) u
join table2 t2 on t2.id = u.region