--每个表写个函数可以(当然,如果你的其他表结构类似,也可以参考下面的书写方式,合并写一个函数) create function f_str( @Id int, @fdname sysname )returns varchar(8000) as begin declare @re varchar(8000) set @re='' if @fdname='ListId' select @re=@re+','+cast(ListId as varchar) from SaleList where id=@id else if @fdname='Product' select @re=@re+','+Product from SaleList where id=@id else if @fdname='Quantity' select @re=@re+','+cast(Quantity as varchar) from SaleList where id=@id else if @fdname='Price' select @re=@re+','+cast(Price as varchar) from SaleList where id=@id else if @fdname='Amount' select @re=@re+','+cast(Amount as varchar) from SaleList where id=@id return(stuff(@re,1,1,'')) end go--调用函数实现你的要求 select Id,AcctDate,[User] ,ListId=dbo.f_str(id,'ListId') ,Product=dbo.f_str(id,'Product') ,Quantity=dbo.f_str(id,'Quantity') ,Price=dbo.f_str(id,'Price') ,Amount=dbo.f_str(id,'Amount') from Sale
选取信息表 (saleList)
匹配字段 (saleList.id)
匹配值 (SaleList.id)
选择字段 (SaleList.Product)
create function f_str(
@Id int,
@fdname sysname
)returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re=''
if @fdname='ListId'
select @re=@re+','+cast(ListId as varchar) from SaleList where id=@id
else if @fdname='Product'
select @re=@re+','+Product from SaleList where id=@id
else if @fdname='Quantity'
select @re=@re+','+cast(Quantity as varchar) from SaleList where id=@id
else if @fdname='Price'
select @re=@re+','+cast(Price as varchar) from SaleList where id=@id
else if @fdname='Amount'
select @re=@re+','+cast(Amount as varchar) from SaleList where id=@id
return(stuff(@re,1,1,''))
end
go--调用函数实现你的要求
select Id,AcctDate,[User]
,ListId=dbo.f_str(id,'ListId')
,Product=dbo.f_str(id,'Product')
,Quantity=dbo.f_str(id,'Quantity')
,Price=dbo.f_str(id,'Price')
,Amount=dbo.f_str(id,'Amount')
from Sale
期待大家的答复