http://community.csdn.net/Expert/TopicView1.asp?id=4735589
查询问题解决了,但是查询结果出现了重复
Part id title DateTime PartId Status
单位1 2 test 2006-5-8 ,5,6, 0
单位2 2 test 2006-5-8 ,5,6, 0我想得到结果如下:
Part id title DateTime PartId Status
单位1,单位2 2 test 2006-5-8 ,5,6, 0请各位高手再帮帮忙!
查询问题解决了,但是查询结果出现了重复
Part id title DateTime PartId Status
单位1 2 test 2006-5-8 ,5,6, 0
单位2 2 test 2006-5-8 ,5,6, 0我想得到结果如下:
Part id title DateTime PartId Status
单位1,单位2 2 test 2006-5-8 ,5,6, 0请各位高手再帮帮忙!
insert into test select '单位1',2,'test','2006-5-8',',5,6,',0
insert into test select '单位2',2,'test','2006-5-8',',5,6,',0
gocreate function f_str(@id int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret=''
select @ret=@ret+Part+',' from test where id=@id
return @ret
end
goselect dbo.f_str(id) as Part,id,title,DateTime,PartId,Status from test group by id,title,DateTime,PartId,Status
/*Part id title DateTime PartId Status
---------------------------------------- ----------- ---------- ---------- ---------- -----------
单位1,单位2, 2 test 2006-5-8 ,5,6, 0
*/
godrop function f_str
drop table test
returns varchar(1000)
as
begin
declare @char varchar(1000)
set @char=''
select @char=@char+part+',' from 表 where id=@id
return(left(@char,len(@char)-1))
endgoselect distinct part=dbo.f_c(id),id,title,[datetime],partid,status from 表
-------------------------------------------------------------------------------------
--创建用户定义函数
create function f_str(@PartId int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret=''
select
@ret=@ret+classname+',' from classname
where
charindex(','+rtrim(id)+',',@PartId)>0 return @ret
end
go--调用函数,执行查询
select
dbo.f_str(PartId),id,title,Datetime,Status
from
EPInfo
order by
Datetime desc,id desc--删除用户定义函数
drop function f_str
insert into test select '单位1',2,'test','2006-5-8',',5,6,',0
insert into test select '单位2',2,'test','2006-5-8',',5,6,',0declare @a varchar(8000),@b int,@c int
select *,cast('' as varchar(8000)) value into #临时表 from test
set @c=0
update #临时表 set @a=case when @b=id then @a else '' end+','+Part
,@c=case when @b=id then @c else @c+1 end,@b=id,Part=@c,value=@aselect Part,id ,title,DateTime,PartId,Status,substring(max(value),2,8000)
from #临时表 group by Part,id,title,DateTime,PartId,Status
go
drop table #临时表,test
-------------------------------------------------------------------------------------
....服务器: 消息 245,级别 16,状态 1,行 1
将 varchar 值 ',1,2,3,4,5,' 转换为数据类型为 int 的列时发生语法错误。
PartId是varchar,怎么函数中用的是int呢?
create function f_str(@PartId int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret=''
select
@ret=@ret+classname+',' from classname
where
charindex(','+rtrim(id)+',',@PartId)>0 return @ret
end
go
改为
create function f_str(@PartId varchar(8000))
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret=''
select
@ret=@ret+classname+',' from classname
where
charindex(','+rtrim(id)+',',@PartId)>0 return @ret
end
go就可以了