SQL 字符列,日期列怎么GROUP BY

SQL 字符列,日期列如何GROUP BY
表结构
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

请赐教.

[解决办法]

SQL code

--建立環境 
  
 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  
 */

SQL code

create table tb (ID varchar(10), Sequence int, Amount int, GoodName varchar(10), CDate datetime )insert into tbselect 'A', 1 , 10 , 'NAMEA1', '2007/01/01' union allselect 'A', 2 , 20 , 'NAMEA2', '2007/01/02' union all select 'A', 3 , 30 , 'NAMEA3', '2007/01/03' union allselect '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 WHERE id = A.id FOR XML AUTO ), '<N GoodName="', ','), '"/>', ''), 1, 1, ''))N) t1,(SELECT * FROM (SELECT DISTINCT id FROM tb)A OUTER APPLY(SELECT CDate = STUFF(REPLACE(REPLACE((SELECT left(convert(varchar(10),CDate,101),5) cdate FROM tb N WHERE id = A.id FOR XML AUTO ), '<N CDate="', ','), '"/>', ''), 1, 1, ''))N) t2where t3.id = t2.id and t3.id = t1.idgroup by t3.id , t1.goodname , t2.cdateorder by t3.iddrop table tb/*id         Amount      GoodName              CDate---------- ----------- --------------------- -----------------A          60          NAMEA1,NAMEA2,NAMEA3  01/01,01/02,01/03B          10          NAMEB1                01/04(2 行受影响)(2 行受影响)*/ 
 
[解决办法]
if object_id('t') is not null
  drop table t
if object_id('f_GoodName') is not null
  drop function f_GoodName
if object_id('f_CDate') is not null
  drop function f_CDate
go
create table t (ID varchar(10),Sequence int,Amount int,GoodName varchar(10),CDate varchar(20))
insert into t
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 * from t
go
create function f_GoodName (@ID varchar(10))
returns varchar(100)
as  
begin
  declare @s varchar(100)
  select  @s=coalesce(@s+',','')+GoodName from t where ID=@ID
  return @s
end
go
create function f_CDate(@ID varchar(10))
returns varchar(100)
as  
begin
  declare @s varchar(100)
  select @s=coalesce(@s+',','')+substring(CDate,6,2) from t where ID=@ID
  return @s
end  
go
select ID,sum(Amount)as 总额 ,dbo.f_GoodName(ID) as GoodName,dbo.f_CDate(ID) as CDate
from t group by ID