结果拼接2010-8-4

现在有表biness和totle表，biness.user_entry_ID=totle.user_entry_ID, 一个biness.user_entry_id可以对应多个totle表中的user_entry_ID
如果biness.user_entry=1,会在totle表里查出四条数据，其中totle表里price,分别会为20，20，40.40

问题：可是我显示的时候想显示（2*20，2*40）注：biness与totle合成一个视图

 

 

答案：

create table biness (user_entry_ID int)
insert into biness  select 1 union all select 2

create table totle(user_entry_ID int,price int)
insert into totle
select 1,20 union all
select 1,20 union all
select 1,40 union all
select 1,40 union all
select 2,10 union all
select 2,30 union all
select 2,40 union all
select 2,50





alter function get_sum(@id int)
returns varchar(100)
as
begin
declare @str varchar(100)

select @str=isnull(@str,'')+','+cast(count(*) as varchar(10))+'*'+ cast(price as varchar(10))   from totle 
where user_entry_ID=@id
group by price  

return stuff(@str,1,1,'')
end

select user_entry_ID,dbo.get_sum(user_entry_ID) from biness