我有一个带有字段的查找表：item\u id，item\u id\u description

ID    ID_Description
501   Horse
601   Mango
701   Trumpet
801   House
901   Cola

我有一个包含字段的人口统计表：name\u id、name、item\u id 1、item\u id 2、item\u id 3、item\u id 4、item\u id 5

Name_ID    Name        Item_ID_1    Item_ID_2    Item_ID_3    Item_ID_4    Item_ID_5
1001       John Smith  701          801          901          NULL         NULL
1002       Jane Smith  901          701          NULL         NULL         NULL

我希望输出文件看起来像：

Name_ID    Name        Item_ID_1    ID_Description Item_ID_2    ID_Description    Item_ID_3    ID_Description     Item_ID_4    Item_ID_5
1001       John Smith  701          Trumpet        801          House             601          Mango          NULL         NULL
1002       Jane Smith  901          Cola           501          Horse             NULL         NULL         NULL

如何生成输出文件：

Name_ID    Name        Item_ID_1    ID_Description Item_ID_2    ID_Description    Item_ID_3    ID_Description     Item_ID_4    Item_ID_5
1001       John Smith  701          Trumpet        801          NA                601          NA  NULL         NULL
1001       John Smith  701          NA             801          House             601          NA NULL         NULL
1001       John Smith  701          NA             801          NA                601          Mango          NULL         NULL
1002       Jane Smith  901          Cola           501          NA                NULL         NULL         NULL
1002       Jane Smith  901          NA             501          Horse             NULL         NULL         NULL

问题：不是在同一行的每个id上添加描述，而是将输出文件拆分并复制行（3x，2x）。如何将描述和ID保持为一行还是多行？
我的代码：

select distinct
d.Name_ID,
d.Name,
d.item_id_1,
Case 
    when d.item_id_1 = l.id
    then l.Item_ID_Description 
    else 'N/A' end as 'ID Description 1',
d.item_id_2,
Case 
    when d.item_id_2 = l.id 
    then l.Item_ID_Description
    else 'N/A' end as 'ID Description 2',   
d.item_id_3,
Case 
    when d.item_id_3 = l.id
    then l.Item_ID_Description
    else 'N/A' end as 'ID Description 3',
d.item_id_4,
Case 
    when d.item_id_4 = l.id
    then l.Item_ID_Description
    else 'N/A' end as 'ID Description 4',
d.item_id_5,
Case 
    when d.item_id_5 = l.id
    then l.Item_ID_Description
    else 'N/A' end as 'ID Description 5'
from [demographic_table] d
left join [lookup_table] l
    on (d.item_id_1 = l.id or 
    d.item_id_2 = l.id or 
    d.item_id_3 = l.id or 
    d.item_id_4 = l.id or 
    d.item_id_5 = l.id)
group by 
d.Name_ID, 
d.Name, 
d.item_id_1, 
d.item_id_2, 
d.item_id_3, 
d.item_id_4, 
d.item_id_5,
l.id,
l.item_id_description