使用 COALESCE 構建逗號分隔的字串
我們可以使用 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