Need suggestion to split string in table 1, match its Ids with table 2 and concatenate the values.
Table - 1
Id Tbl1Col
1 2
2 2,4
3
4 6
5 3
Table - 2
Id Tbl2Col
1 E
2 F
3 M
4 U
5 P
6 C
7 N
8 G
Query -
SELECT T2.Tbl2Col
FROM Table1 AS T1
LEFT JOIN Table2 AS T2 WHERE T1.Tbl1Col= T2.Id
WHERE T1.Id = @Id
Now If @Id = 1, Output is F -- works fine
Now If @Id = 2, Output should be FU -- should not be F,U
Yuck! But you can use LIKE:
SELECT T2.Tbl2Col
FROM Table1 T1 LEFT JOIN
Table2 T2
WHERE ',' + T1.Tbl1Col + ',' LIKE '%,' + CAST(T2.Id as VARCHAR(255)) + ',%'
WHERE T1.Id = @Id;
You have a lousy data format, so this cannot make use of indexes. You should really have a separate table, with one row per Table1.id and Table2.id. Such a table is called a junction table or an association table.
create table dbo.Table01 (
Id int
, Col varchar(100)
);
create table dbo.Table02 (
Id int
, Col varchar(100)
);
insert into dbo.Table01 (Id, Col)
values (1, '2'), (2, '2, 4');
insert into dbo.Table02 (Id, Col)
values (1, 'E'), (2, 'F'), (4, 'U');
select
t.Id
, replace(STRING_AGG (t02.Col, ','), ',', '') as StringAgg
from dbo.Table01 t
cross apply string_split (t.Col, ',') as ss
inner join dbo.Table02 t02 on ss.value = t02.Id
group by t.id
Follow the next approach:-
1) Turning a Comma Separated string into individual rows via using CROSS APPLY with XML
2) Join the two tables with left join.
3) Concatenate many rows with same id via using STUFF & FOR XML
4) Use Replace function for removing comma.
Demo:-
declare @MyTable table (id int , Tbl1Col varchar(10))
insert into @MyTable values (1,'2'),(2,'2,4'),(3,''),(4,'6'),(5,'3')
declare @MyTable2 table (id int , Tbl2Col varchar(10))
insert into @MyTable2 values (1,'E'),(2,'F'),(3,'M'),(4,'U'),(5,'P'),(6,'C'),(7,'N'),(8,'G')
select a.id , Tbl2Col
into #TestTable
from
(
SELECT A.id,
Split.a.value('.', 'VARCHAR(100)') AS Tbl1Col
FROM
(
SELECT id,
CAST ('<M>' + REPLACE(Tbl1Col, ',', '</M><M>') + '</M>' AS XML) AS Data
FROM @MyTable
) AS A CROSS APPLY Data.nodes ('/M') AS Split(a) ) a
left join @MyTable2 b
on a.Tbl1Col = b.id
order by a.id
SELECT id, Tbl2Col =
Replace(STUFF((SELECT DISTINCT ', ' + Tbl2Col
FROM #TestTable b
WHERE b.id = a.id
FOR XML PATH('')), 1, 2, ''),',','')
FROM #TestTable a
GROUP BY id
Output:-
1 F
2 F U
3 NULL
4 C
5 M
References:-
Turning a Comma Separated string into individual rows
How to concatenate many rows with same id in sql?
Don't use this approach, and normalize your database instead , just use it as fun/training/trying .... etc code.
