Concat/Join select result columns by char x

Question

set @alenzi = 'select ' + @IdentityColumns + ' from ' + @Tablename + ' where ' + @x
exec (@alenzi )

returns 1 to n columns. Means @IdentityColumns is a comma separated string. How can I concat the result with a char like '^'

For instance now I get 3 colums
Column Header: |A|B|C
Row 1: |a|b|c
Row 2: |d|e|f

but I want:
Column Header: |A
Row 1: |a^b^c
Row 2: |d^e^f

The solution has to work with old MSSql Server, too.

Answer 1

If it is not very old, then check this modification:

 set @alenzi = 'select concat_ws(''^'', ' + @IdentityColumns + ') as A from ' + . . .

Answer 2

Hi @regae alternate ,

Welcome to the microsoft TSQL Q&A forum!

The following method is applicable to all supported versions SQL Server:

set @sql = 'select ' + REPLACE(@IdentityColumns,',','^') + ' from ' + @Tablename + ' where ' + @x  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Ok, thanks! concat_ws delivers the correct result. But it has to work with MsSql Server 2012. I tried to implement that with concat. Here is a simplified test to show the next issue:

select REPLACE('PATIENTPK,PATNR', ',', ',''^'',') from patient

returns

PATIENTPK,'^',PATNR

for each row of the patient table. If I copy and paste the return value into

select concat(PATIENTPK,'^',PATNR) from patient

The result ist correct. For instance

1784^1

Which means PATIENTPK and PATNR have been handled as column names, which is like should be.

Now I tried to combine both and wraped the replace with concat:

select concat(REPLACE('PATIENTPK,PATNR', ',', ',''^'',')) from patient

That returns the error:
The concat function requires 2 to 254 arguments.
It seems like concat interprets the return value of replace as 1 argument. How can I change that?

Answer 4

If you have to use REPLACE instead of CONCAT_WS, then try this:

 set @alenzi = 'select ' + replace(@IdentityColumns, ',', '+''^''+') + ' as A from ' + . . .

It assumes that names do not contain ','.

Maybe it is better to adjust the code that builds the @IdentityColumns variable.