求SQL行列合併統計
item hdate
a2006/01/02
b2006/01/02
c2006/01/03
d2006/01/03
e2006/01/03
f2006/01/05
g2006/01/06
==>
countitemhdate
2ab2006/01/02
3cde2006/01/03
1f2006/01/05
1g2006/01/06
[解决办法]
create table T(item varchar(10), hdate datetime)
insert T select 'a ', '2006/01/02 '
union all select 'b ', '2006/01/02 '
union all select 'c ', '2006/01/03 '
union all select 'd ', '2006/01/03 '
union all select 'e ', '2006/01/03 '
union all select 'f ', '2006/01/05 '
union all select 'g ', '2006/01/06 '
create function fun(@hdate datetime)
returns varchar(100)
as
begin
declare @re varchar(100)
set @re= ' '
select @re=@re+item from T where hdate=@hdate
return @re
end
select num=count(*), itme=dbo.fun(hdate), hdate from T group by hdate
--result
num itme hdate
----------- ---------------------------------------------------------------- ------------------------------------------------------
2 ab 2006-01-02 00:00:00.000
3 cde 2006-01-03 00:00:00.000
1 f 2006-01-05 00:00:00.000
1 g 2006-01-06 00:00:00.000
(4 row(s) affected)
[解决办法]
轻轻的我来了，正如我要轻轻的说：
。。。。。楼上正解~~
