--建立環境 
  
 create table a(ID varchar(20),       Sequence   int,    Amount  int,     GoodName  varchar(20),   CDate  datetime ) 
 insert into  a    
 select 'A' ,        1,             10,           'NAMEA1',       '2007/01/01'  union all 
 select'A' ,        2,             20,           'NAMEA2',       '2007/01/02'  union all 
 select'A',         3,             30,           'NAMEA3',       '2007/01/03'  union all 
 select'B' ,        1,             10,           'NAMEB1' ,      '2008/01/04'   
  
 --函數1 
  
 create  function f_str(@id varchar(20)) 
 returns varchar(1000) 
 as  
 begin  
 declare @sql varchar(8000) 
 set @sql='' ,@sql 
 select @sql=@sql+','+GoodName from a  where id=@id 
 set @sql=stuff(@sql,1,1,'') 
 return(@sql) 
 end 
  
 --函數2  
  
 create function f_date(@id varchar(20)) 
 returns varchar(1000) 
 as  
 begin  
 declare @sql varchar(8000) 
 set @sql='' 
 select @sql=@sql+','+right('0'+convert (varchar(2),datepart(mm,CDate)),2)+'/'+right('0'+convert (varchar(2),datepart(dd,CDate)),2) from a  where id=@id 
 set @sql=stuff(@sql,1,1,'') 
 return(@sql) 
 end 
  
  
 --查詢語句 
  
 select id,sum(amount) as amount,dbo.f_str(id) as GoodName,dbo.f_date(id) as CDate  from a  group by id 
  
 /* 
 结果  
 ------------------ 
 ID     Amount       GoodName                         CDate  
 A       60           NAMEA1,NAMEA2,NAMEA3       01/01,01/02,01/03  
 B       10           NAMEB1                          01/04  
 */

create table tb (ID varchar(10), Sequence int, Amount int, GoodName varchar(10), CDate datetime )
insert into tb
select 'A', 1 , 10 , 'NAMEA1', '2007/01/01' union all
select 'A', 2 , 20 , 'NAMEA2', '2007/01/02' union all 
select 'A', 3 , 30 , 'NAMEA3', '2007/01/03' union all
select 'B', 1 , 10 , 'NAMEB1', '2008/01/04' 

select t3.id , Amount = sum(t3.Amount) , t1.GoodName , t2.CDate from tb t3,
(SELECT * FROM (SELECT DISTINCT id FROM tb)A OUTER APPLY(SELECT GoodName = STUFF(REPLACE(REPLACE((SELECT GoodName FROM tb N WHER