SQL query to join comma separated values

Question

Hi have a table that has Owner ID which is a array of users. Below is my tables.

Task table

ID    Task_Name   Task_Owner
1      Create      1,3,4
2       Edit       2,1,3
3      Delete      2,1,4

Owner table:

ID        Owner_Name
1           Mike
2           Ken
3           Lim
4           Nick

And I need an output that fetch all names

Output:

ID    Task Name      Owners
1     Create         Mike,Lim, Nick
2     Edit           Ken, Mike, Lim
3     Delete         Ken, Mike, Nick

Are you really after a generic sql solution, or do you want this for a specific database platform? If so, please tag the database.. Also just be aware, this is not a good database design. — Nick.Mc, Jul 31 '23 at 03:51
[Why should I "tag my RDBMS"?](https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms) - please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Jul 31 '23 at 04:06
Actually I'm using Zoho Analaytics. So I'm just after a generic SQL solution — Johny, Jul 31 '23 at 04:48
Storing several values in one string violates the very first normal of database normalization. This is not how to use a relational database. It makes writing queries more difficult and can lead to inconsistencies. Change your database design so as to have one row per value. — Thorsten Kettner, Jul 31 '23 at 07:01

score 0 · Accepted Answer · answered Jul 31 '23 at 04:01

It is not a good practice, you should normalize your database by creating other table with the relationship, or use the next code, just replace for the correct name of your columns and table, Also see Solution

select
m.id,
m.taskname
group_concat(c.ownername) from
tasktable m
join ownertable c on find_in_set(c.id, m.taskowner) group by
m.id

score 0 · Answer 2 · answered Jul 31 '23 at 04:16

If you are looking for something that will run on Microsoft SQL Server then the previous example (using group_concat) will not run as that function is unavailable on that platform. This should meet your requirements:

SELECT
  t.ID,
  t.Task_Name AS [Task Name],
  STRING_AGG(o.Owner_Name, ', ') AS Owners
FROM
  [Task_Table] t
JOIN
  [Owner_Table] o ON o.ID IN 
    (
        SELECT [value] FROM STRING_SPLIT(t.Task_owner, ',')
    )
GROUP BY
  t.ID, 
  t.Task_Name

SQL query to join comma separated values

2 Answers2