Split comma separated data and get its respective value from another table-CodePudding

I have concated data in table1

id	concats	sum
1	b,c
2	a,k,f,l,s
3	b,f,t
4	a,b,h,k,l,q,s,t
5	b,c,k,f,p,s
6	a,c,q,s

and another table with value

grade	score
a	4.82
b	2.65
c	2.56
d	2.75
g	6.90
h	5.90
k	6.41
f	12.80
l	2.56
p	12.80
q	1.35
s	2.90
t	5.97

I want to update table1.sum, something like b,c=(2.65 2.56=5.21)

Tried the below mentioned code, but there is an error.

UPDATE table1 as t1 SET sum = 
     (SELECT (CASE WHEN (SELECT SPLIT_PART(concats,',',1) from t1) = t2.grade then t2.score ELSE 0 END)  
             (CASE WHEN (SELECT SPLIT_PART(concats,',',2) from t1) = t2.grade then t2.score ELSE 0 END)  
             (CASE WHEN (SELECT SPLIT_PART(concats,',',3) from t1) = t2.grade then t2.score ELSE 0 END)  
             (CASE WHEN (SELECT SPLIT_PART(concats,',',4) from t1) = t2.grade then t2.score ELSE 0 END)  
             (CASE WHEN (SELECT SPLIT_PART(concats,',',5) from t1) = t2.grade then t2.score ELSE 0 END)  
             (CASE WHEN (SELECT SPLIT_PART(concats,',',6) from t1) = t2.grade then t2.score ELSE 0 END)  
             (CASE WHEN (SELECT SPLIT_PART(concats,',',7) from t1) = t2.grade then t2.score ELSE 0 END )  
             (CASE WHEN (SELECT SPLIT_PART(concats,',',8) from t1) = t2.grade then t2.score ELSE 0 END ) 
         FROM table2 AS t2 )

CodePudding user response：

You can join the two tables by converting the dreaded CSV columns to an array, then do the GROUP BY and sum on the result of that. This can be used to update the target table:

update table1
  set sum = x.sum_score
from (  
  select t1.id,
         sum(t2.score) as sum_score
  from table1 t1
    join table2 t2 on t2.grade = any(string_to_array(t1.concats, ',')) 
  group by t1.id
) x
where x.id = table1.id;