How to aggregate two tables on a comma-separated string field?

user_16547177 0
Viewed 157

In one table I have a column with multiple values separated by a comma. In the second, columns with id and name. I need to get names separated by a slash.

Table1:

id value1 ids value2
1 abcd 1,2,3 efgh
2 ijkl 1,4,6 mnop

Table2:

id name
1 1AB
2 2CD
3 3EF
4 4GH
5 5IJ
6 6KL 
SELECT
    a.*,  
    GROUP_CONCAT(b.name,'/') groupedName
FROM
    table1 a
LEFT JOIN
    table2 b ON b.id IN (a.ids)
WHERE
    a.id = 1

Result:

id value1 ids value2 groupedName
1 abcd 1,2,3 efgh 1AB/2CD/3EF
group-concat join sqlite sql
2 Answers

Given that your "Table1.ids" has VARCHAR(n) type, you can use the LIKE function for the matching condition on the JOIN between the two tables, only then apply the GROUP_CONCAT aggregation function:

SELECT t1.id,
       t1.value1,
       t1.ids,
       t1.value2,
       GROUP_CONCAT(t2.name, '/') AS groupedName 
FROM       table1 t1
INNER JOIN table2 t2
        ON t1.ids LIKE t2.id || ',%'
        OR t1.ids LIKE  '%,' || t2.id || ',%'
        OR t1.ids LIKE           '%,' || t2.id
        OR t1.ids = t2.ids
GROUP BY t1.id,
         t1.value1,
         t1.ids,
         t1.value2

Check the demo here.

user_124928900

Assuming that id is the primary key of table1, use the operator LIKE in the ON clause like this:

SELECT t1.*,  
       GROUP_CONCAT(t2.name, '/') groupedName
FROM table1 t1 LEFT JOIN table2 t2 
ON ',' || t1.ids || ',' LIKE '%,' || t2.id || ',%'
GROUP BY t1.id;

See the demo.

user_104988280
Related