0

I have the following 2 tables

Table1 ( ID, val1, val2)
Table2 ( ID, val3, val4)

this is the condition table1.val1 = table2.val3

For each table1.val1 there can be multiple rows in table2.

If I am joining as following then there will be multiple select for the same table1 values.

select t1.*,t2.val4
from table1 t1, table t2
where t1.val1 = t2.val3


0, 1, 2, 4
0, 1, 2, 5
0, 1, 2, 6

and so on.

I want to contact all the val4 of table2 as following

0, 1, 2, "4/5/6"
flyingfox
  • 13,414
  • 3
  • 24
  • 39
John
  • 35
  • 4
  • 1
    What version of SQL Server are you working with? Please read the instructions in the [Sql-Server tag info](https://stackoverflow.com/tags/sql-server/info) and [edit] your question accordingly. – Zohar Peled Jul 26 '21 at 11:35
  • 1
    I typed "Concatenating row values in SQL" into an internet search and [this answer](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) was the first in the search results. – Dan Guzman Jul 26 '21 at 11:37
  • Concatenating strings is server specific, PostgreSQL, for instance, uses `||`, others can differ, though, some have a `CONCAT` function for. – Aconcagua Jul 26 '21 at 11:37

2 Answers2

0

From what you've shown I would suggest using STRING_AGG with GROUP BY clause.

SELECT t1.ID, t1.val1, t1.val2, STRING_AGG(t2.val4, '/')
FROM table1 t1
JOIN table2 t2
ON t1.val1 = t2.val3
GROUP BY t1.ID, t1.val1, t1.val2
ginkul
  • 1,026
  • 1
  • 5
  • 17
0

select t1.*,Value4=stuff(select ',' + val4 from table2 t2 where t1.val1 = t2.val3 for xml path(''),type).value('.','nvarchar(max)'),1,1,'') from table1 t1

Atique Rahman
  • 151
  • 5