Converting n columns to delimited string from text only subquery

Question

This is for Microsoft SQL Server 2016 (SP1) - 13.0.4001.0

I have a rather annoying bit of data that needs to be converted into a comma separated string. My options are limited due to it being a 3rd party software that replaces text in my query and runs it. For example, I will write the following:

SELECT %myvalues% for xml path('')

Which then gets turned into:

SELECT 'test1','test2','test3'...'testn' for xml path('')

Which returns

test1test2test3...testn

This works, but it doesn't separate the text with commas or spaces. Here's the result I want:

test1, test2, test3, ... testn

The problem is, I can't control how it inserts the text. I did find the STUFF function among a bunch of other solutions but none seem to work when I don't know the column names.

For example, I get:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Sounds lie the problem here is the application, not the SQL. — Thom A, Jan 03 '19 at 16:24
Possible duplicate of [How to make a query with group\_concat in sql server](https://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) — S3S, Jan 03 '19 at 16:25

score 0 · Accepted Answer · answered Jan 24 '19 at 19:24

I have no idea if this is the best way to do it, but I have had success with the following method:

IF OBJECT_ID('mytable', 'U') IS NOT NULL DROP TABLE mytable; 
CREATE TABLE mytable ("@remove@'mylist', 'Ofannoying', 'comma', 'seperated values'" INT NULL);
SELECT REPLACE(REPLACE((SELECT c.Name FROM sys.columns c INNER JOIN sys.objects o ON o.object_id = c.object_id WHERE o.name = 'mytable'), '''', ''), '@remove@', '')

Where my actual code looks something like this:

CREATE TABLE mytable ("@remove@#myvalues#" INT NULL);

Converting n columns to delimited string from text only subquery

1 Answers1