Microsoft SQL Server 使用COALESCE构建逗号分隔的字符串
示例
我们可以使用合并从多个行中获取一个逗号分隔的字符串,如下所示。
由于使用了表变量,因此我们需要执行一次整个查询。为了易于理解,我添加了BEGIN和END块。
BEGIN --Table variable declaration to store sample records DECLARE @Table TABLE (FirstName varchar(256), LastName varchar(256)) --Inserting sample records into table variable @Table INSERT INTO @Table (FirstName, LastName) VALUES ('John','Smith'), ('Jane','Doe') --Creating variable to store result DECLARE @Names varchar(4000) --Used COLESCE function, so it will concatenate comma seperated FirstName into @Names varible SELECT @Names = COALESCE(@Names + ',', '') + FirstName FROM @Table --Now selecting actual result SELECT @Names END