I have sql server management studio 14.0.17825.0 and would like to use group_concat function. But I get error when i try to use. The error is invalid column name group_concat Is there any other function that I could use? Could you provide a sample code which could achieve what function group_concat?
Asked
Active
Viewed 153 times
-1
Gordon Linoff
- 1,198,228
- 53
- 572
- 709
user2543622
- 4,900
- 22
- 75
- 136
1 Answers
0
In SQL Server 2017+, the function is called string_agg():
select string_agg(col, ',') within group (order by col)
In earlier versions, you use a trick with XML, that might look like:
select stuff( (select ',' + col
from t
for xml path ('')
), 1, 1, ''
)
Gordon Linoff
- 1,198,228
- 53
- 572
- 709
-
why do we need `within group (order by col)`? is it possible to use `distinct` such as `select string_agg(distinct (col), ',')` – user2543622 Sep 12 '20 at 01:22
-
i get an error `'string_agg is not a recognized built in function name.` – user2543622 Sep 12 '20 at 01:32
-
1@user2543622 . . . I think the question addresses those issues. – Gordon Linoff Sep 12 '20 at 01:36
-
that worked but how do i get the other columns in the data? for example I have two columns `col1, col2` and i would like to get data for `col2` concatnated for each value of 'col1' – user2543622 Sep 12 '20 at 14:55
-
@user2543622 . . . You would have a different question and that should be asked as *new* question. – Gordon Linoff Sep 13 '20 at 01:38