表结构
ID nchar(10)
Sequence int
Amount int
GoodName nchar(30)
CDate datetime
***ID和Sequence主键***
数据
ID Sequence Amount GoodName CDate
A 1 10 NAMEA1 2007/01/01
A 2 20 NAMEA2 2007/01/02
A 3 30 NAMEA3 2007/01/03
B 1 10 NAMEB1 2008/01/04
想要的结果
ID Amount GoodName CDate
A 60 NAMEA1,NAMEA2,NAMEA3 01/01,01/02,01/03
B 10 NAMEB1 01/04
--建立環境
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
*/