数据库中存在记录例如:
ID Name PID
1 a 1
2 b 1
3 c 1
4 d 1
5 e 1请问如何用SQL语句实现下列语句的搜索:
PID Name
1 a b c d e解决就结贴
ID Name PID
1 a 1
2 b 1
3 c 1
4 d 1
5 e 1请问如何用SQL语句实现下列语句的搜索:
PID Name
1 a b c d e解决就结贴
http://community.csdn.net/Expert/topic/5137/5137568.xml?temp=.4559137
有表rowtocol,其数据如下:
a b
1 1
1 2
1 3
2 1
2 2
3 1
如何转换成如下结果:
a b
1 1,2,3
2 1,2
3 1 创建一个合并的函数 create function f_rowtocol(@a int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(b as varchar) from rowtocol where a = @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
调用自定义函数得到结果:
select distinct a ,dbo.f_rowtocol(a) from rowtocol
drop table test
go
if object_id('pubs..f_hb') is not null
drop function f_hb
gocreate table test
(
id int,
name varchar(10),
pid int
)insert into test(id,name,pid) values(1,'a',1)
insert into test(id,name,pid) values(2,'b',1)
insert into test(id,name,pid) values(3,'c',1)
insert into test(id,name,pid) values(4,'d',1)
insert into test(id,name,pid) values(5,'e',1)
go--创建一个合并的函数
create function f_hb(@pid int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ' ' + cast(name as varchar) from test where pid = @pid
set @str = right(@str , len(@str) - 1)
return(@str)
End
go--调用自定义函数得到结果:
select distinct pid ,dbo.f_hb(pid) as 结果 from testdrop table test--结果
pid 结果
----------- ---------
1 a b c d e(所影响的行数为 1 行)