符合條件的結果集，將特定欄位的字串相加

以AdvantureWorks為DB，使用Person.Contact Table


-- 目的：取出LastName='Ferrier'的人，將其FirstName字串相加
--1.計算LastName='Ferrier'的筆數
--2.建立變數：NameCount(筆數)、n(取n筆資料的第一筆)、st(存放字串)
--3.跑回圈、累加字串

declare @NameCount int --取筆數
set @NameCount = (select count(*) from Person.Contact where LastName='Ferrier')
--select @NameCount
declare @n int --第n筆
set @n = 2

declare @st varchar(1000) --名字累加
set @st = (select top 1 FirstName from Person.Contact where LastName='Ferrier' order by FirstName)

while(@n <=@NameCount)
begin
set @st = @st + ';' + (select top 1 FirstName from
(select top(@n) FirstName from Person.Contact where LastName='Ferrier' order by FirstName asc)
tmp order by FirstName desc)
set @n =@n +1
end

select @st