1

I am using SQL Server Management Studio v15.0 and I'm trying to use GROUP_CONCAT() or STUFF() functions to get the concatenated result. But I am unable to draft a correct query.

I am getting the errors mentioned in the tagline.

I have 2 tables, User and Userlanguages.

A single user can have multiple languages associated. I want these multiple languages in a single row separated by a semi-colon (;)

My Userlanguages table looks like this:

User Language
ANDY English
ANDY Korean
ANDY Spanish
TOM English
TOM Spanish

Expected output :

User Language
ANDY English; Korean; Spanish
TOM English; Spanish

Please suggest a proper SQL query to achieve this output.

SQL query used :

SELECT  
    [User].[userID]
    STUFF((SELECT ', ' + [UserLanguages].[languagesID]
           FROM UserLanguages
           WHERE [User].[userID] = [UserLanguages].[userID_student]
           FOR XML PATH('')), 1, 1, '')
FROM 
    User;

This is the error I get:

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'FOR'.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
CCA
  • 11
  • 1
  • 3
  • 3
    SQL Server has [STRING_AGG](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15) function – Lukasz Szozda May 31 '21 at 15:34
  • 1
    You're missing a comma after `User.userID`. You also need a `GROUP BY userID` and the subquery should finish `FOR XML PATH(''), TYPE).value('text()[1]', 'nvarchar(max)'), 1, 2, '')` – Charlieface May 31 '21 at 15:43
  • I am receiving the below error after doing the suggested changes. Conversion failed when converting the varchar value ', ' to data type int. – CCA May 31 '21 at 16:08
  • use Cast([UserLanguages].[languagesID] as varchar) instead of [UserLanguages].[languagesID] – PankajSanwal May 31 '21 at 16:17
  • Your version of SSMS is irrelevant - SSMS is only the client GUI, V15.0 is not even in the current list of supported versions. What version of SQL Server are you using? Ie the latest 2019 or a prior version 2017/2016/2014 etc? – Stu May 31 '21 at 19:30

0 Answers0