• 主页

# 结果集合并问题，急

mark2006 2007-12-15 10:43:30

...全文
19 点赞 收藏 1

1 条回复

bqb 2007-12-15
--写个函数

http://topic.csdn.net/u/20071215/14/b75165c7-3647-43ae-8e40-daf2af59512d.html

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
*/

6094