日期:2014-05-17 浏览次数:20751 次
create table #tableTest
(ID int identity,姓名 varchar(20),报读科目 varchar(20),费用 money)
go
insert into #tableTest
select '小明','语文',300 union all
select '小明','数学',500 union all
select '小明','英语',500 union all
select '小红','语文',300 union all
select '小红','数学',500
go
select *from #tableTest
create table tableTest
(ID int identity,姓名 varchar(20),报读科目 varchar(20),费用 money)
go
insert into tableTest
select '小明','语文',300 union all
select '小明','数学',500 union all
select '小明','英语',500 union all
select '小红','语文',300 union all
select '小红','数学',500
go
select *from tableTest
create function dbo.fn_mergeSTR(@name varchar(20),@split varchar(10))
returns varchar(300)
as
begin
declare @str varchar(300);
set @str = '';
select @str = @str + 报读科目 + @split
from tableTest
where 姓名 = @name
set @str = left(@str , len(@str) - LEN(@split) )
return @str --返回值
end
go
select 姓名,
报读科目,
SUM(费用) as 费用
from
(
select 姓名,
dbo.fn_mergeSTR(姓名,'
SQL2005怎么为每个对象生成脚本