sql-从查找表联接-将输出文件上卷到单行与多行中

nuypyhwy  于 2021-08-09  发布在  Java
关注(0)|答案(1)|浏览(298)

我有一个带有字段的查找表: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
xkftehaa

xkftehaa1#

DECLARE @Demographic TABLE(
    [Name_ID] [int] NULL,
    [Name] [nchar](50) NULL,
    [Item_ID_1] [int] NULL,
    [Item_ID_2] [int] NULL,
    [Item_ID_3] [int] NULL,
    [Item_ID_4] [int] NULL,
    [Item_ID_5] [int] NULL
)

DECLARE @Lookup TABLE(
    [ID] [int] NULL,
    [ID_Description] [varchar](50) NULL
)

INSERT @Demographic ([Name_ID], [Name], [Item_ID_1], [Item_ID_2], [Item_ID_3], [Item_ID_4], [Item_ID_5]) VALUES (1001, N'John Dough', 701, 801, 601, NULL, NULL)
INSERT @Demographic ([Name_ID], [Name], [Item_ID_1], [Item_ID_2], [Item_ID_3], [Item_ID_4], [Item_ID_5]) VALUES (1002, N'Jane Smith', 901, 501, NULL, NULL, NULL)
INSERT @Lookup ([ID], [ID_Description]) VALUES (501, N'Horse')
INSERT @Lookup ([ID], [ID_Description]) VALUES (601, N'Mango')
INSERT @Lookup ([ID], [ID_Description]) VALUES (701, N'Trumpet')
INSERT @Lookup ([ID], [ID_Description]) VALUES (801, N'House')
INSERT @Lookup ([ID], [ID_Description]) VALUES (901, N'Cola')

;with Itemz as (select ID,ID_Description FROM @Lookup)
Select d.Name,d.Name_ID,i1.id,i1.ID_Description,i2.id,i2.ID_Description,i3.id,i3.ID_Description,i4.id,i4.ID_Description,i5.id,i5.ID_Description from @Demographic d
left Join Itemz i1 on i1.id=d.Item_ID_1
left Join Itemz i2 on i2.id=d.Item_ID_2
left Join Itemz i3 on i3.id=d.Item_ID_3
left Join Itemz i4 on i4.id=d.Item_ID_4
left Join Itemz i5 on i5.id=d.Item_ID_5

相关问题