0

I am trying to convert multiple rows of column into single row using comma , delimiter in SQL Server.

I tried this code, but I am getting parsing error:

SELECT ID + ', ' As 'Data()'
FROM Test_schema.Table_name 
FOR XML PATH('')

where test_schema is schema name and Table_name is table name with 2000 distinct IDs and 8 columns.

What am I doing wrong here?

Dale K
  • 25,246
  • 15
  • 42
  • 71
pravin wattamwar
  • 1
  • 1
  • The statement you have given us doesn't generate that error. – Thom A Jul 26 '21 at 09:19
  • Single quotes are used for literal strings, not for aliases. XML doesn't like parentheses so remove them (this caused your error): `ID + ', ' As [Data]` (If ID is not a string datatype you'll need to cast it first). This still won't give you the result you are looking for, I think. There are many extensive examples on how to do this (search for something like `GROUP_CONCAT in SQL SERVER`) – HoneyBadger Jul 26 '21 at 09:31
  • I tried the aliasing as you mentioned and also checked data type and it is string., but still same error – pravin wattamwar Jul 26 '21 at 09:38
  • @Larnu do i need to change any settings? to work FOR XML Path – pravin wattamwar Jul 26 '21 at 09:39
  • Does this answer your question? [How to concatenate text from multiple rows into a single text string in SQL server?](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) – Charlieface Jul 26 '21 at 13:54

1 Answers1

1

Firstly, the SQL you have won't generate the error you state you get. If you try running the above you get the following error:

Column name 'Data()' contains an invalid XML identifier as required by FOR XML; '('(0x0028) is the first character at fault.

This is because XML nodes can't have parenthesis (()) in their name.

It sounds, like, however, that you shouldn't even be aliasing as you want a concatenated list of all your ID's from your table. If so, then your query should actually look like this:

SELECT STUFF((SELECT CONCAT(', ',ID) --I assume ID could be a non-string type
              FROM Test_schema.Table_name 
              FOR XML PATH(''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,2,N'');

If, however, you're on a more recent version of SQL Server, just us STRING_AGG:

SELECT STRING_AGG(ID,', ')
FROM Test_schema.Table_name;
Thom A
  • 88,727
  • 11
  • 45
  • 75