How to add items from another table based on a string aggregated column-CodePudding

I have 2 tables like this

[Table 1]:

|cust_id| tran |item  |
| ------| -----|-------
| id1   | 123  |a,b,c |
| id2   | 234  |b,b   |
| id3   | 345  |c,d,a,b|

[Table 2]:

| item. | value |
| ----- | ----- |
| a     | 1     |
| b     | 2     |
| c     | 3     |
| d     | 4     |

I want to create a target value by doing a lookup from table 2 in table 1 using big query.

|cust_id| tran.|item  |target|
| ------| -----|------|------|
| id1   | 123  |a,b,c | 6
| id2   | 234  |b,b   | 4
| id3   | 345  |c,d,a,b| 10

What can I try next?

CodePudding user response：

Consider below simple approach

select *, 
  ( select sum(value)
    from unnest(split(item)) item
    join table2 
    using (item)
  ) target
from table1

if applied to sample data in your question - output is

CodePudding user response：

Try the following:

select t1.cust_id
    , t1.tran
    , t1.item
    , sum(t2.value) as target
from table_1 t1
    , UNNEST(split(t1.item ,',')) as item_unnested
LEFT JOIN table_2 t2
 on item_unnested=t2.item
group by t1.cust_id
    , t1.tran
    , t1.item

With your data it gives the following:

CodePudding user response：

Create a center table that splits the item column values on rows and join that table with table2. Try following

--Cursor is used to split the item data row by row
--#temp is a temporary table

create table #temp (id varchar(10), trans varchar(10), item varchar(10), item1 varchar(10));

DECLARE @item  varchar(10);
DECLARE @id varchar(10);
DECLARE @trans varchar(10);


DECLARE item_cusor CURSOR FOR
SELECT * 
FROM   table1;

OPEN item_cusor

FETCH NEXT FROM item_cusor
INTO @id,@trans,@item


WHILE @@FETCH_STATUS = 0
BEGIN
    insert into #temp
    SELECT @id,@trans,@item,* 
    FROM   STRING_SPLIT (@item, ',')


    FETCH NEXT FROM item_cusor
INTO @id,@trans,@item

END
CLOSE item_cusor;
DEALLOCATE item_cusor;

--select * from  temp

select t.id as cust_id, t.trans,t.item , sum(cast(t2.value as int)) as target 
from #temp t
JOIN table2 t2
on t.item1=t2.item
group by  t.id, t.trans,t.item;

Cursors: https://www.c-sharpcorner.com/article/cursors-in-sql-server/

Temporary tables: https://www.sqlservertutorial.net/sql-server-basics/sql-server-temporary-tables/

String split function: https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql