*奇想西卡*

將多各筆資料塞入一各欄位 ， 參考網頁

http://omnibuzz-sql.blogspot.com/2006/06/concatenate-values-in-column-in-sql.html

因為需要抓出某人的某月負責哪些客戶，但是資料庫設計如下。

COPZI
ZI001(YM)  ZI002(ID) ZI003(PID)
---------------------------------
200801     97006   4LO
200801     97006 4OM
200801     97006 4BQ

因為是在SQL 2000 環境上，不能直接用一行指令完成
寫成 FUNCTION 來call

create function Concat (@Col1 varchar(10) , @Col2 varchar(10) )
returns varchar(1000)
as
begin
declare @resultStr varchar(1000)
select @resultStr = coalesce(@resultStr,'') + ','+ ZI003 from COPZI where ZI001 = @Col1 AND ZI002 = @Col2
return stuff(@resultStr,1,1,'')
end

call FUNCTION 的語法跟結果

Select ZI001 , ZI002  ,dbo.Concat(ZI001 , ZI002 ) AS ZI003
from COPZI
group by ZI001 , ZI002
order by ZI001 , ZI002

ZI001 ZI002 ZI003
-----------------------------
200801 97006 4LO,4OM,4BQ