sql - Convert a multi columnrow table output into a single string - Stack Overflow

admin管理员组

文章数量:1332339

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

ColumnA	ColumnB	ColumnC	ColumnD
date1	datavalue1	identifier1	description1
date2	datavalue2	identifier2	description2

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

ColumnA	ColumnB	ColumnC	ColumnD
date1	datavalue1	identifier1	description1
date2	datavalue2	identifier2	description2

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

Share Improve this question Follow edited Nov 20, 2024 at 19:53 Dale K 27.5k1515 gold badges5858 silver badges8383 bronze badges asked Nov 20, 2024 at 19:52 TheDownsideUpCodingTheDownsideUpCoding 1111 bronze badge 15

• 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

 |  Show 10 more comments

2 Answers 2

Sorted by: Reset to default 0

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

本文标签： sqlConvert a multi columnrow table output into a single stringStack Overflow