SQL - string combine based on id

Question

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

You should **not** be storing comma separated values in the database — James Z, Jun 07 '17 at 14:19
@James Z, I didn't create these tables, have to work with them anyhow. — Sajal, Jun 07 '17 at 14:33

score 1 · Accepted Answer · answered Jun 07 '17 at 14:29

1

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.

answered Jun 07 '17 at 14:29

Gordon Linoff

1,242,037
58
646
786

I understand normalization, have to work with this data format. Let me test this. – Sajal Jun 07 '17 at 14:35
Had to `select @Codes = concat(@codes + T2.Tbl2Col)` to get the desired result as `FU` rather than in multiple rows. Still, worked like a charm! – Sajal Jun 07 '17 at 15:37

score 1 · Answer 2 · answered Jun 07 '17 at 14:31

1

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

answered Jun 07 '17 at 14:31

Dean Savović

739
3
7

Your code is not working ! **Msg 195, Level 15, State 10, Line 19 'STRING_AGG' is not a recognized built-in function name.** – ahmed abdelqader Jun 07 '17 at 14:34
On which SQL Server version are you on? – Dean Savović Jun 07 '17 at 18:07
@ahmedabdelqader, yes this is true. You can use custom functions for string aggs. Then it works on SQL 2016+. Also if you need to work on SQL 2014- then use custom function for string_split. – Dean Savović Jun 08 '17 at 09:49

score 1 · Answer 3 · answered Jun 07 '17 at 15:30

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?

Finally:-

Don't use this approach, and normalize your database instead , just use it as fun/training/trying .... etc code.

Tables were created by other devs few years back, I cannot alter them, well because of dependencies. I will test your solution too, seems interesting! — Sajal, Jun 07 '17 at 15:39

SQL - string combine based on id

3 Answers3

Finally:-

Linked