SQL server Lookup table from another table-CodePudding

I would like to use SQL Server something like lookup table in Excel.

It is not easy to explain, so I'll show you examples.

Example tableA,

Type1        Type2
---------------------
 A            F         
 B            G
 C            H
 D            I
 E           NULL

TableB

ID     Country     AllTypes            
---------------------------------
1       Italy      A, B, C
2       USA        D, E, A, F   
4       Japan      I         
5       UK         NULL

By using these two tables, I would like to get the output such as

ID     Country     AllTypes            Type1          Type2            
----------------------------------------------------------
1       Italy      A, B, C            A, B, C        NULL
2       USA        D, E, G, F         D, E           G, F
4       Japan      I                  NULL           I       
5       UK         NULL               NULL           NULL

I could not use Excel since my original data has more than 1 million rows.

Can you please help me how to approach for this?

I think I can use Full outer join but how can I do for (if AllTypes has Type1 values show them in Type 1 column).

CodePudding user response：

Having multiple delimited values in a single column is always going to be problematic, one way is to use a combination of string_split and string_agg if you are using SQL Server 2017

select b.Id, b.Country, b.Alltypes,
    String_Agg(v.type1,',') Type1,
    String_Agg(v.type2,',') Type2
from tableb b
outer apply (
    select Trim(value) t,
        case when exists 
          (select * from tablea a where a.type1=Trim(value)) 
        then Trim(value) end type1,
        case when exists
          (select * from tablea a where a.type2=Trim(value)) 
        then Trim(value) end type2
    from String_Split(alltypes, ',')
)v
group by Id, Country, AllTypes

CodePudding user response：

Too long for a comment. Any chance you can refactor the DB? I would suggest

tableA
col1 Type
A     1
B     1
..
F     2
G     2

Country
id  Name
1   Italy      
2   USA 

Country_TableA
countryId  aId
1      A
1      B

CodePudding user response：

As commented previously, combining string_split and string_agg allows you to reach what you want. This is my (shorter) version:

select
  b.ID, b.Country, b.Alltypes, 
  string_agg(a.Type1, ',') as Type1,
  string_agg(aa.Type2, ',') as  Type2
from TableB b
  outer apply string_split(b.Alltypes, ',')
  left join TableA a
    on a.Type1 = ltrim(rtrim(value))
  left join TableA aa
    on aa.Type2 = ltrim(rtrim(value))    
group by b.ID, b.Country, b.Alltypes

You can test on this db<>fiddle