先在存储过程中的一段sql执行结果是select View_MC_Letter_SumBill_Letter.debtorid,View_MC_Letter_SumBill_Letter.DebtorName,View_MC_Bill_Letter_One.unitno From View_MC_Bill_Letter_One INNER JOIN View_MC_Letter_SumBill_Letter ON View_MC_Letter_SumBill_Letter.DebtorID = View_MC_Bill_Letter_One.DebtorID inner join mc_property on View_MC_Bill_Letter_One.propertyid=mc_property.propertyid wHERE (View_MC_Bill_Letter_One.DebtorID IS NOT NULL) AND (CAST(View_MC_Bill_Letter_One.TotalAmount AS money) <> 0) and View_MC_Bill_Letter_One.elementCode in ('MaintainFee','MeterFee','OtherFee','CarParkFee')
-- and View_MC_Letter_SumBill_Letter.debtorid=269
group by View_MC_Letter_SumBill_Letter.DebtorID,View_MC_Letter_SumBill_Letter.DebtorName,View_MC_Bill_Letter_One.unitno
debtorname unitno
kfc f1-
星吧客 BGLL501
霍妙清 BGLL501
上海多金多餐饮有限公司 f2
上海多金多餐饮有限公司 BGLL503-
梁爽 f3
梁爽 BGLL504
李四 f4
李四 BGLL505
测试 f5
测试 BGLL403
相经过筛选后得到结果nameNuitno
f1-"这里是空格"kfc
BGLL501-"这里是空格"星吧客
BGLL501-"这里是空格"霍妙清
f2,BGLL503-"这里是空格"上海多金多餐饮有限公司
f3,BGLL503-"这里是空格"梁爽
f4,BGLL504"这里是空格"李四
f5,BGLL403"这里是空格"测试大家帮忙看下
-- and View_MC_Letter_SumBill_Letter.debtorid=269
group by View_MC_Letter_SumBill_Letter.DebtorID,View_MC_Letter_SumBill_Letter.DebtorName,View_MC_Bill_Letter_One.unitno
debtorname unitno
kfc f1-
星吧客 BGLL501
霍妙清 BGLL501
上海多金多餐饮有限公司 f2
上海多金多餐饮有限公司 BGLL503-
梁爽 f3
梁爽 BGLL504
李四 f4
李四 BGLL505
测试 f5
测试 BGLL403
相经过筛选后得到结果nameNuitno
f1-"这里是空格"kfc
BGLL501-"这里是空格"星吧客
BGLL501-"这里是空格"霍妙清
f2,BGLL503-"这里是空格"上海多金多餐饮有限公司
f3,BGLL503-"这里是空格"梁爽
f4,BGLL504"这里是空格"李四
f5,BGLL403"这里是空格"测试大家帮忙看下
declare @t table(debrotname nvarchar(50),unitno nvarchar(20))insert into @t
select 'kfc','f1-' union all
select '星吧客','BGLL501' union all
select '霍妙清','BGLL501' union all
select '上海多金多餐饮有限公司','f2' union all
select '上海多金多餐饮有限公司','BGLL503' union all
select '梁爽','f3-' union all
select '梁爽','BGLL504' union all
select '李四','f4' union all
select '李四','BGLL505' union all
select '测试','f5' union all
select '测试','BGLL403'--create function hebing(@tname nvarchar(20))
--as
--放在函数中,'李四'用参数传进来
declare @s nvarchar(100)
select @s = isnull(@s+',','') + ltrim(unitno) from @t where debrotname = '李四'
select @s
-------------
f4,BGLL505
select
stuff((select ','+unitno from t
where debtorname=t1.debtorname
order by len(unitno)
for xml path('')),1,1,'') +
'" "'+debtorname [nameNuitno]
from t t1
group by debtorname
select (case when right(rtrim(unitno),1) = '-' then rtrim(unitno) else rtrim(unitno)+'-' end) +'"这里是空格"'+ltrim(rtrim(debtorname)) from tb
这样?
create table t(debrotname nvarchar(50),unitno nvarchar(20))insert into t
select 'kfc','f1-' union all
select '星吧客','BGLL501' union all
select '霍妙清','BGLL501' union all
select '上海多金多餐饮有限公司','f2' union all
select '上海多金多餐饮有限公司','BGLL503' union all
select '梁爽','f3-' union all
select '梁爽','BGLL504' union all
select '李四','f4' union all
select '李四','BGLL505' union all
select '测试','f5' union all
select '测试','BGLL403'create function hebing(@tname nvarchar(20))
returns nvarchar(50)
as
begin
declare @s nvarchar(100)
select @s = isnull(@s+',','') + ltrim(unitno) from t where debrotname = @tname
return @s
endselect dbo.hebing(debrotname) + ' ' + debrotname from t group by debrotname-----------------------------------
f1- kfc
f5,BGLL403 测试
BGLL501 霍妙清
f4,BGLL505 李四
f3-,BGLL504 梁爽
f2,BGLL503 上海多金多餐饮有限公司
BGLL501 星吧客