Split comma separated values based on another table-CodePudding

I would like to split comma separated values based on another table

I cannot normalize it since original table has over 8 million rows. It crushed my laptop when I tried it.

How can I put data into relevant columns and create a new column if data is not found.

For 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, O, Z        
5       UK         NULL

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

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

This is I have done so far

with TableA as (
  select 'A' as Type1, 'F' as Type2 union all
  select 'B', 'G' union all
  select 'C', 'H' union all
  select 'D', 'I' union all
  select 'E', NULL
),
TableB as (
  select 1 as ID, 'Italy' as Country, 'A, B, C' as Alltypes union all
  select 2, 'USA', 'D, E, A, F' union all  
  select 4, 'Japan', 'I', 'O', 'Z' union all
  select 5, 'UK', NULL
)

select b.Id, b.Country, b.Alltypes,
    String_Agg(v.type1,',') Type1,
    String_Agg(v.type2,',') Type2
    **String_Agg(v.Type3,',') Uncaptured*** ------- This query
    
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,
        Case when not exists                                  ------------This query
          ( (select * from tablea a where a.type1=Trim(value)) -------
             and                                               ------
             (select * from tablea a where a.type2=Trim(value))------
          ) then Trim(value) end Type3**                        -------------
    from String_Split(alltypes, ',')
)v
group by Id, Country, AllTypes

Without highlighted queries(-----) which are for creating a new column (Uncaptured), it works ok like below.

Id  Country Alltypes    Type1   Type2
1   Italy   A, B, C     A,B,C   NULL
2   USA     D, E, A, F  D,E,A   F
4   Japan   I, O, Z     I       NULL
5   UK      NULL        NULL    NULL

But if I add those highlighted queries, it shows error. I was also thinking of else but did not work as well.

Could someone help me please?

CodePudding user response：

How about

outer apply (
    select Trim(value) t, a1.type1, a2.type2,
        CASE WHEN COALESCE(a1.type1, a2.type2) IS NULL THEN Trim(s.value) END unCaptured
    from String_Split(alltypes, ',') s
    left join tablea a1 where a1.type1=Trim(s.value)
    left join tablea a2 where a2.type2=Trim(s.value)
)v

CodePudding user response：

----------------------- DDL DML: Should have been provided by the OP !
DROP TABLE IF EXISTS TableA,TableB
GO

create table TableA(Type1 CHAR(1), Type2 char(1))
GO
INSERT TableA (Type1,Type2) VALUES
    ('A', 'F'  ),        
    ('B', 'G'  ),
    ('C', 'H'  ),
    ('D', 'I'  ),
    ('E', NULL )
GO

CREATE TABLE TableB (ID INT, Country NVARCHAR(100), AllTypes NVARCHAR(100))
GO
INSERT TableB (ID,Country,AllTypes)VALUES 
(1, 'Italy','A, B, C'     ),
(2, 'USA  ','D, E, G, F'  ),
(4, 'Japan','I, O, Z'     ),
(5, 'UK   ','NULL'        )
GO

----------------------- Solution
;WITH MyCTE AS (
    SELECT ID,Country,AllTypes, MyType = TRIM([value])
    FROM TableB
    CROSS APPLY string_split(AllTypes,',')
)
,MyCTE02 as (
    SELECT ID,Country,AllTypes, MyType,a1.Type1,a2.Type2,
        UnCaptured = CASE WHEN a1.Type1 IS NULL and a2.Type2 IS NULL THEN MyType END
    FROM MyCTE c
    LEFT JOIN TableA a1 ON c.MyType = a1.Type1
    LEFT JOIN TableA a2 ON c.MyType = a2.Type2
)
SELECT ID,Country,AllTypes--,MyType
    ,Type1 = STRING_AGG(Type1,','),Type2 = STRING_AGG(Type2,','),UnCaptured = STRING_AGG(UnCaptured,',')
FROM MyCTE02
GROUP BY ID,Country,AllTypes
GO