-- sql 2005 select stuff((sekect ','+branch from table where name=t.name for xml path('')),1,1,'') [branch], name, sum(money) [money] from table t group by name
select Branch=stuff((select ','+Branch from tb where Name=t.Name for xml path('')),1,1,''), [Name], sum([Money])[Money] from tb t group by [name]
select stuff((select ','+branch from [table] where [name]=a.[name] for xml path('')),1,1,'') as branch, a.[name],sum(a.[money]) [money] from [table] a group by a.[name]
你们都太强大了。。我总算弄明白stuff()这个函数是咋个回事了。。
看起来用2000落伍了,给个2000的吧 IF EXISTS (SELECT * FROM sysobjects WHERE NAME ='table1') DROP TABLE [table1] CREATE TABLE [table1] ( [Branch] varchar(100) NULL , [Name] varchar(100) NULL , [Money] NUMERIC(18,2) NULL ) GO--插入测试数据 INSERT INTO [table1] ([Branch],[Name],[Money]) SELECT 'a','Zhang','1000.00' UNION SELECT 'b','Wang','500.00' UNION SELECT 'c','Li','700.00' UNION SELECT 'd','Zhu','300.00' UNION SELECT 'a','Zhu','600.00' UNION SELECT 'c','Zhu','700.00' UNION SELECT 'a','Wang','500.00' GO --create function f_t(@name1 VARCHAR(100)) returns nvarchar(4000) as begin declare @s nvarchar(4000) set @s='' select @s=@s+','+branch from table1 where [NAME]=@name1 return(stuff(@s,1,1,'')) end go --调用 select NAME,dbo.f_t(NAME),SUM(MONEY) from table1 group by NAME/* Li c 700.00 Wang a,b 1000.00 Zhang a 1000.00 Zhu a,c,d 1600.00(4 row(s) affected)*/
if OBJECT_ID('a') is not null drop table a gocreate table a ( branch varchar(10), name varchar(20), tmoney decimal(18,2) ) goinsert into a select 'a','Zhang',1000.00 union all select 'b','Wang',500.00 union all select 'c','Li',700.00 union all select 'd','Zhu',300.00 union all select 'a','Zhu',600.00 union all select 'c','Zhu',700.00 union all select 'a','Wang',500.00 goselect distinct branch=stuff((select ','+ltrim(branch) from a where name=b.name order by branch for xml path('')),1,1,'') ,name ,tmoney=(select SUM(tmoney) from a where name=b.name group by name) from a b go
select distinct branch=stuff((select ','+ltrim(branch) from a where name=b.name order by branch for xml path('')),1,1,'') ,name ,SUM(tmoney) tmoney from a b group by name go
from tb
where Name=t.Name
for xml path('')),1,1,''),
[Name],
sum([Money])[Money]
from tb t
group by [name]
where [name]=a.[name] for xml path('')),1,1,'') as branch,
a.[name],sum(a.[money]) [money]
from [table] a
group by a.[name]
IF EXISTS (SELECT * FROM sysobjects WHERE NAME ='table1')
DROP TABLE [table1]
CREATE TABLE [table1]
(
[Branch] varchar(100) NULL ,
[Name] varchar(100) NULL ,
[Money] NUMERIC(18,2) NULL
)
GO--插入测试数据
INSERT INTO [table1] ([Branch],[Name],[Money])
SELECT 'a','Zhang','1000.00' UNION
SELECT 'b','Wang','500.00' UNION
SELECT 'c','Li','700.00' UNION
SELECT 'd','Zhu','300.00' UNION
SELECT 'a','Zhu','600.00' UNION
SELECT 'c','Zhu','700.00' UNION
SELECT 'a','Wang','500.00'
GO
--create function f_t(@name1 VARCHAR(100))
returns nvarchar(4000)
as
begin
declare @s nvarchar(4000)
set @s=''
select @s=@s+','+branch from table1 where [NAME]=@name1
return(stuff(@s,1,1,''))
end
go
--调用
select NAME,dbo.f_t(NAME),SUM(MONEY) from table1 group by NAME/*
Li c 700.00
Wang a,b 1000.00
Zhang a 1000.00
Zhu a,c,d 1600.00(4 row(s) affected)*/
有没有简单的方法
if OBJECT_ID('a') is not null
drop table a
gocreate table a
(
branch varchar(10),
name varchar(20),
tmoney decimal(18,2)
)
goinsert into a
select 'a','Zhang',1000.00
union all
select 'b','Wang',500.00
union all
select 'c','Li',700.00
union all
select 'd','Zhu',300.00
union all
select 'a','Zhu',600.00
union all
select 'c','Zhu',700.00
union all
select 'a','Wang',500.00
goselect distinct branch=stuff((select ','+ltrim(branch) from a where name=b.name order by branch for xml path('')),1,1,'')
,name
,tmoney=(select SUM(tmoney) from a where name=b.name group by name)
from a b
go
select distinct branch=stuff((select ','+ltrim(branch) from a where name=b.name order by branch for xml path('')),1,1,'')
,name
,SUM(tmoney) tmoney
from a b
group by name
go