select name, fruit=stuff((select ','+fruit from tb where name=t.name for xml path('')),1,1,'') from tb t group by name
select name ,[fruit]=stuff((select ','+fruit from tb where name=T.name for xml path('')),1,1,'')from tb T
---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([name] varchar(4),[fruit] varchar(4)) insert [tb] select '小王','苹果' union all select '小王','梨' union all select '小王','香蕉'
---查询--- select name, fruit=stuff((select ','+fruit from tb where name=t.name for xml path('')),1,1,'') from tb t group by name---结果--- name fruit ---- ------------------------ 小王 苹果,梨,香蕉(1 行受影响)
select name ,[fruit]=stuff((select ','+fruit from tb where name=T.name for xml path('')),1,1,'')from tb T group by T.name
select name, [fruit]=stuff((select ','+[fruit] from tb t where name=tb.name for xml path('')), 1, 1, '') from tb group by name
--建一个类似这样的函数create function dbo.f_str(@name varchar(10)) returns varchar(100) as begin declare @str varchar(1000) set @str = '' select @str = @str + ',' + fruit from tb where name = @name set @str = right(@str , len(@str) - 1) return @str end select name,fruit=dbo.f_str(name) from tb group by name
if exists(select * from sysobjects where [name]='table1') drop table table1 create table table1 ( [name] varchar(50), fruit varchar(50) ) go insert into table1([name],fruit) ( select '小王','苹果' union select '小王','梨' union select '小王','香蕉 ' ) goselect [name] , fruit = stuff((select ',' + Convert(varchar(50),fruit) from table1 where [name] =a.[name] for xml path('')),1,1,'') from table1 a group by [name]
select name, fruit=stuff((select ','+ fruit from tt where name=t.name for xml path('')),1,1,'') from tt t group by name
Create Function F(@Name VarChar(10)) Returns VarChar(100) As Begin Declare @Str VarChar(100) Select @Str = IsNull(@Str + ',' + Fruit,Fruit) From Tb Where Name = @Name Return @Str End Go Select Name,DBO.F(Name) Fruit From Tb Group By Name
fruit=stuff((select ','+fruit from tb where name=t.name for xml path('')),1,1,'')
from tb t
group by name
,[fruit]=stuff((select ','+fruit from tb where name=T.name for xml path('')),1,1,'')from tb T
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([name] varchar(4),[fruit] varchar(4))
insert [tb]
select '小王','苹果' union all
select '小王','梨' union all
select '小王','香蕉'
---查询---
select name,
fruit=stuff((select ','+fruit from tb where name=t.name for xml path('')),1,1,'')
from tb t
group by name---结果---
name fruit
---- ------------------------
小王 苹果,梨,香蕉(1 行受影响)
,[fruit]=stuff((select ','+fruit from tb where name=T.name for xml path('')),1,1,'')from tb T group by T.name
from tb
group by name
as
begin
declare @str varchar(1000)
set @str = ''
select @str = @str + ',' + fruit from tb where name = @name
set @str = right(@str , len(@str) - 1)
return @str
end
select name,fruit=dbo.f_str(name) from tb group by name
drop table table1
create table table1
(
[name] varchar(50),
fruit varchar(50)
)
go
insert into table1([name],fruit)
(
select '小王','苹果' union
select '小王','梨' union
select '小王','香蕉 '
)
goselect [name]
, fruit = stuff((select ',' + Convert(varchar(50),fruit)
from table1
where [name] =a.[name] for xml path('')),1,1,'')
from table1 a
group by [name]
fruit=stuff((select ','+ fruit from tt where name=t.name for xml path('')),1,1,'')
from tt t
group by name
Returns VarChar(100)
As
Begin
Declare @Str VarChar(100)
Select @Str = IsNull(@Str + ',' + Fruit,Fruit) From Tb Where Name = @Name
Return @Str
End
Go
Select Name,DBO.F(Name) Fruit From Tb Group By Name