我有一个带有字段的查找表: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
1条答案
按热度按时间xkftehaa1#