Convert comma separated values and join with different tables in Microsoft SQL 2019

Question

There are three tables and column have comma separated values as shown below: In table 1 there is a single value(TableValue1) and some values needs to get it from table2(Table2Name1 - get values on basis of name in table 2). In table 3 there is a group of sets present in table 2(Table3Name1 - need to fetch values from table 2 on basis of name).

Table 1 :

ID	Name	Values
1	test 1	Table1Value1,Table2Name1,Table3Name1
2	test 2	Table1Value2,Table2Name2,Table2Name4,Table3Name2

Table 2 :

Name	Values
Table2Name1	A,B,C
Table2Name2	D,E,F
Table2Name3	G,H
Table2Name4	I,J,K

Table 3:

Name	Values
Table3Name1	Table2Name1,Table2Name3
Table3Name2	Table2Name2,Table2Name3

Result, needs to be like below :

ID	Name	Values
1	test 1	table1value1,A,B,C,G,H
2	test 2	Table1Value2,D,E,F,I,J,K,G,H

select * 
from ( select tbl.id,tbl.name,tbl.value,table2.value 
       from (select id,name,value 
             from table1 
             cross apply string_split(data,',')) as tbl 
       left join table2 on tbl.value = table2.name) as A 
       left join(select b.table3name,tb.value,table2.[values]
                 from(select tbl3.table3name,tbl3.value 
                      from(select tbl3.name,tbl3.[values],value
                           from table3 
                           cross apply string_split(tbl3.[values],',')) as tbl3) as tb 
left join table2 on table2.name = tb.value) as B on A.value = B.name

but its not showing correct data.

This seems like an awful way of storing data. You really should consider a massive change in your database design. — Zohar Peled, Apr 02 '23 at 08:16
I think this is an awesome new paradigm, key value table name. People have gotten Nobel prize for less — siggemannen, Apr 02 '23 at 08:57
@siggemannen , updated the query but it's not working as expected. — Posting, Apr 02 '23 at 09:54
@ Yitzhak Khabinsky , updated it but not showing the expected result. — Posting, Apr 02 '23 at 09:55

score 0 · Answer 1 · answered Apr 02 '23 at 17:55

Here's my take on the issue:

;WITH table1 AS (
SELECT  *
FROM    (
    VALUES  (1, N'test 1', N'Table1Value1,Table2Name1,Table3Name1')
    ,   (2, N'test 2', N'Table1Value2,Table2Name2,Table2Name4,Table3Name2')
) t (ID,Name,[values])
)
, table2 AS (
SELECT  *
FROM    (
    VALUES  (N'Table2Name1', N'A,B,C')
    ,   (N'Table2Name2', N'D,E,F')
    ,   (N'Table2Name3', N'G,H')
    ,   (N'Table2Name4', N'I,J,K')
) t (Name,[Values])
)
,table3 AS (
SELECT  *
FROM    (
    VALUES  (N'Table3Name1', N'Table2Name1,Table2Name3')
    ,   (N'Table3Name2', N'Table2Name2,Table2Name3')
) t (Name,[Values])
)
SELECT  id, name, STRING_AGG(val, ',') WITHIN GROUP (ORDER BY sort, val)
FROM    (
    SELECT  t1.id, t1.name, MIN(sort) AS sort
    ,   z.val
    FROM    table1 t1
    CROSS APPLY string_split(t1.[values], ',') x
    LEFT JOIN table2 t2
        ON  t2.name = x.value
    LEFT JOIN table3 t3
        ON  t3.[name] = x.value
    OUTER APPLY string_split(t3.[values], ',') x2
    LEFT JOIN table2 t22
        ON  t22.name = x2.value
    CROSS APPLY (
        SELECT  val, sort
        FROM    (
            VALUES  (CASE WHEN t2.name IS NULL AND t3.name IS NULL THEN x.value END, 1)
            ,   (t2.[values], 2)
            ,   (t22.[values],2)
            ) v (val, sort)
        WHERE   v.val IS NOT NULL
        ) z
    GROUP BY t1.id, t1.name, z.val
    ) x
GROUP BY id, name

I basically just follow the rabbithole of string splits from various tables. Finally, i collect the values from table2 together with what's left over from values in table1, do a grouping to remove duplicate and finally STRING_AGG them together to some fort of CSV string.

As others said, this database design ain't good, as Codd et.al. said, one should never combine multiple values into a single column, and definitely don't combine key / value data in the same column.

Can you please read these two comments?: [one](https://stackoverflow.com/questions/710683/when-should-i-use-semicolons-in-sql-server#comment60413127_31742373) and [two](https://stackoverflow.com/questions/710683/when-should-i-use-semicolons-in-sql-server#comment110475624_31742373) — Luuk, Apr 03 '23 at 17:36
I read them! And i know it's technically not needed, but i still like to do it to avoid issues in the long run — siggemannen, Apr 03 '23 at 18:15
So, you are also typing `;SELECT` to avoid issues in the long run ? — Luuk, Apr 03 '23 at 19:21
LOL, no, but if you prepend some other statement in front of with, it usually complains, so it's easier to do it at once and move on with your life — siggemannen, Apr 03 '23 at 19:28

dp808139 · Answer 2 · 2023-04-03T05:08:30.997

I tried but I am not sure it is not an optimized solution. But it is the exact solution. and more readable query.

My table names:

table1 (id int, nam varchar(100), val varchar(100))

table2 (name varchar(100),val varchar(100))

table3(name varchar(100), val varchar(100))

with cte as(select t1.id,t1.name,value as val from my_db.dbo.table1 t1 CROSS APPLY STRING_SPLIT(val, ',')),
cte2 as(select t1.id, t1.name, t1.val, case when t2.val is null then t1.val else t2.val end as val1 from cte t1 left join table2 t2 on t1.val=t2.name) ,
cte3 as(select ct2.id,ct2.name ,case when t3.val is null then ct2.val1 else t3.val end as val from cte2 ct2 left join table3 t3 on t3.name = ct2.val),
cte4 as(select id,name,value from cte3 CROSS APPLY STRING_SPLIT(val, ',')),
cte5 as(select ct4.id,ct4.name,case when t2.val is null then ct4.value else t2.val end as val from cte4 ct4 left join table2 t2 on ct4.value=t2.name),
cte6 as(select id,name as name,value as val from cte5 CROSS APPLY STRING_SPLIT(val, ',') group by id,name,value)
select id as 'ID',name as 'Name',STRING_AGG(val,', ') within group (order by case when len(val)>1 then 0 else 1 end, val) as 'Values' from cte6 group by id,name;

Convert comma separated values and join with different tables in Microsoft SQL 2019

2 Answers2