將多各筆資料塞入一各欄位 , 參考網頁
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