--表结构如下
create table tb(bus nvarchar(10),station nvarchar(50))
insert tb select '1路' ,'洪家坡'
union all select '1路','驷马桥'
union all select '1路' ,'南大街'
union all select '2路','荷花池'
union all select '2路' ,'高笋塘'
go--最后查询结果
bus station
1路 洪家坡|驷马桥|南大街
2路 荷花池|高笋塘
create table tb(bus nvarchar(10),station nvarchar(50))
insert tb select '1路' ,'洪家坡'
union all select '1路','驷马桥'
union all select '1路' ,'南大街'
union all select '2路','荷花池'
union all select '2路' ,'高笋塘'
go--最后查询结果
bus station
1路 洪家坡|驷马桥|南大街
2路 荷花池|高笋塘
--测试环境
create table tb(bus nvarchar(10),station nvarchar(50))
insert tb select '1路' ,'洪家坡'
union all select '1路','驷马桥'
union all select '1路' ,'南大街'
union all select '2路','荷花池'
union all select '2路' ,'高笋塘'
go
--函数
Create function F_GetStr(@bus as varchar(10))
returns Varchar(30)
As
Begin
declare @returnValue as varchar(30)
set @returnValue =''
select @returnValue=@returnValue +','+rtrim(ltrim(station)) from
(select station from tb where bus=@bus) Areturn stuff(@returnValue,1,1,'')
End--查询
select bus,station=dbo.F_GetStr(bus) from tb
group by bus
--结果
bus station
---------- ------------------------------
1路 洪家坡,驷马桥,南大街
2路 荷花池,高笋塘(所影响的行数为 2 行)
--删除测试环境
drop table TB
drop function F_GetStr
--函数
ALTER function F_GetStr(@bus as varchar(10))
returns Varchar(30)
As
Begin
declare @returnValue as varchar(30)
set @returnValue =''
select @returnValue=@returnValue +'|'+rtrim(ltrim(station)) from
(select station from tb where bus=@bus) Areturn stuff(@returnValue,1,1,'')
End--查询
select bus,station=dbo.F_GetStr(bus) from tb
group by bus
--结果
bus station
---------- ------------------------------
1路 洪家坡|驷马桥|南大街
2路 荷花池|高笋塘(所影响的行数为 2 行)
--删除测试环境
drop table TB
drop function F_GetStr
returns nvarchar(4000)
as
begin
declare @ret nvarchar(4000)
set @ret = N''
select @ret = @ret + N'|'+station from tb where bus = @bus
set @ret = stuff(@ret,1,1,'')
return @ret
end
go
select bus,station=dbo.f_str(bus) from tb group by bus
declare @returnValue as varchar(30) =>
declare @returnValue as varchar(500)
http://community.csdn.net/Expert/topic/4186/4186501.xml?temp=.6206324
一句比较复杂的SQL语句不知如何写?又有字符合并,又有数值相加!具体见内!