Search code examples
sqlsql-serverjoinconcatenation

How to join rows in some table in SQL Server?

i want join some table then concatenation columns

MyTable

-------------------------------
ID   RowId   LangId   Caption
-------------------------------
1    1       1        ڕۆشتن
2    1       2        Go
3    1       3        اذهب
4    2       1        ئاو
5    2       2        water   
6    2       3        ماء

I want join concatenation Caption column ex: for RowId 1 'ڕۆشتن - Go - اذهب'

Desired output

--------------------
RowId   Caption
--------------------
1       ڕۆشتن - Go - اذهب
2       ئاو- water - ماء

I seen link but can't help me

Solution

  • You can use string_agg():

    select rowid,
       string_agg(caption, ' ') within group (order by langid) as caption
from t
group by rowid;