*************求一SQL语句**************

[jilm168]

有如下表:
errcode location count
---------------------------------------------
15:23 U3 168
15:23 C5910 117
15:23 C1617 103

15:20 J4001 715
15:20 J4000 558
15:20 J7600 293

.......

有没有办法选择出这样的结果:
errcode desc
--------------------------------------------
15:23 U3*168;C5910*117;C1617*103
15:20 J4001*715;J4000*558;J7600*293
.....

[zjcxc]

--写个自定义函数处理
create function f_str(@errcode char(5))
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+';'+rtrim(location)+'*'+rtrim([count])
from 表 where errcode=@errcode
return(stuff(@r,1,1,''))
end
go

--调用实现查询
select errcode,[desc]=dbo.f_str(errcode) from 表 group by errcode

[631799]

select aa.errcode,aa.a+';'+bb.b+';'+cc.c from
(
select errcode,a=max(cast(location as varchar)+'*'+cast(count as varchar)) from tb group by errcode
) aa
left join
(
select errcode,b=min(cast(location as varchar)+'*'+cast(count as varchar)) from tb group by errcode
) bb
on aa.errcode=bb.errcode
left join
(
select errcode,c=


(select t.* from
(select cast(location as varchar)+'*'+cast(count as varchar) from tb) t where cast(location as varchar)+'*'+cast(count as varchar)
not in (select max(cast(location as varchar)+'*'+cast(count as varchar)) from tb group by errcode union all
select min(cast(location as varchar)+'*'+cast(count as varchar)) from tb group by errcode )

)

from tb group by errcode
) cc
on bb.errcode =cc.errcode

[jilm168]

为了普遍性,我希望把表的名字也作为参数传进去,怎么写呀?

[zlp321002]

函数中不支持动态表,
把邹建的函数改写成存储过程就可以了！

Create proc p_str
(@errcode varchar(5),
@TableName varchar(20),
@outValue varchar(8000) output
)
as
begin
set @outValue=''
declare @sqls as nvarchar(4000)
set @sqls=''
set @sqls='select @r=@r+'';''+rtrim(location)+''*''+rtrim([count])
from '+@TableName+' where errcode='+@errcode+' '
exec sp_executesql @sqls,N'@r as varchar(8000) out', @outValue output
select @outValue
end
go
--测试
declare @output as varchar(8000)
exec p_str '参数','表名',@output output