I have a table called #table1, larger but similar to the one below. I want to move the results to a single string variable.

I have a table called #table1, larger but similar to the one below. I want to move the results to a single string variable.

I want the table to reference the table as a string variable in a format where columns are separated by an underscore and rows separated by a comma - such as:

@var = 'date1_datavalue1_identifier1_description1,date2_datavalue2_identifier2_description2'

I have done this manually on a smaller scale with the below

select convert(varchar,columnA) + '_' + convert(varchar,ColumnB) + '_' + convert(varchar,columnC) + '_' + convert(varchar,columnD) as comment
from #table1 

Is this possible in SQL and is there a way to do this dynamically that isn't restricted by number of table rows?

• sql
• sql-server
• t-sql
• data-analysis

• 3 string_agg? And you must provide a size when you convert to varchar else you might truncate your values. – Dale K Commented Nov 20, 2024 at 19:55
• 2 Why do you "need" to do this in T-SQL? What is the actual end goal? This smells like an XY Problem. – Thom A Commented Nov 20, 2024 at 20:05
• 1 I'm not sure STRING_AGG is the solution here, @DaleK ; that is for delimiting rows, not columns. – Thom A Commented Nov 20, 2024 at 20:21
• 3 It's possible, but let's say you succeed, what's next? Are you gonna frame the variable on your wall or something? It won't be of any use unless you want to transport it somewhere, for that reason, i'd probably go with FOR JSON or FOR XML instead – siggemannen Commented Nov 20, 2024 at 20:23
• 1 True, probably maybe concat_ws – Dale K Commented Nov 20, 2024 at 20:24

2 Answers 2

You can try below code sample.

dbfiddle Link : https://dbfiddle.uk/W-ZfeIlg

DECLARE @var varchar(max)

SELECT @var = STUFF((SELECT ',' + convert(varchar,columnA) + '_' + convert(varchar,ColumnB) + '_' 
                           + convert(varchar,columnC) + '_' + convert(varchar,columnD)
                    FROM table1 
                    FOR XML PATH('')), 1, 1, '')

SELECT @var as comment

For SQL Server 2017

be careful with string limit,null values, implicit conversions about string agg function

use string_agg and concat function

sample

DECLARE @comments VARCHAR(MAX) = (
SELECT STRING_AGG(CONCAT_WS('_',ColumnA,ColumnB,ColumnC,ColumnD),',')
  FROM TABLE1)

SELECT @comments as comment