SQL：如何将列的所有行的值作为单个值？ [重复]

This question already has an answer here:

这个问题在这里已有答案：

• Simulating group_concat MySQL function in Microsoft SQL Server 2005? 9 answers
• 在Microsoft SQL Server 2005中模拟group_concat MySQL函数？ 9个答案

I am working with SQL Server 2008 R2.

我正在使用SQL Server 2008 R2。

This is my table:

这是我的表：

CREATE TABLE step
(
   id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
   step_value nvarchar(max) NOT NULL
)

Currently there are 197 rows in step table. I want to get all values of step_value column as a single value with a semi-colon delimiter. How can I do that?

目前步骤表中有197行。我想将step_value列的所有值作为带有分号分隔符的单个值。我怎样才能做到这一点？

Some sample data:

一些样本数据：

Insert Into step(step_value)
Values ('a'),('b'),('c'),('d'),('<workflow name="DISCIPLINE_LETTER">'), ('   <target>MEETING_INITIAL_MEETING_LETTER</target> ')

Thanks

谢谢

2 个解决方案

#1

Use STUFF function

使用STUFF功能

select stuff(
 (select '; ' + step_value 
 from step
 for xml path(''), root('MyString'), type
 ).value('/MyString[1]','nvarchar(max)')
, 1, 2, '') as step_values ;

FIDDLE

小提琴

#2

SELECT TOP 1 (STUFF((SELECT ', ' + CAST(id AS VARCHAR(MAX)) 
         FROM step 
         FOR XML PATH ('')), 1, 2, '')) AS a
FROM step