SQL SERVER 2000将一列多行数据合并

SQL语句现在查询的结果：



期望实现的结果：


 SQL Server 2000的数据库，用sql语句查询时，将上面表中的数据合并为下表的格式，有办法吗？

6 个解决方案

#1

写个函数合并字符串，对于金额可以直接求和。

#2

付款说明那里可以用case when来做

#3

select 
 付款单号,应付对象,单据日期,币种,SUM(实付金额),SUM(核销金额),SUM(本次核销金额),
 (select cast(款项类别+','+来源单号 as varchar) from TB where 条件=t.条件 for xml path('') )
 from TB t group by 付款单号,应付对象,单据日期,币种

#4

引用 3 楼 xqchenxue2 的回复:

select 
 付款单号,应付对象,单据日期,币种,SUM(实付金额),SUM(核销金额),SUM(本次核销金额),
 (select cast(款项类别+','+来源单号 as varchar) from TB where 条件=t.条件 for xml path('') )
 from TB t group by 付款单号,应付对象,单据日期,币种

2000不支持for xml path 函数

#5

类似于这样

if object_id('tempdb..#Tmp_Payment') is not null
								drop table #Tmp_Payment

CREATE TABLE #Tmp_Payment (
             Payment_No  varchar(30) ,
             Taget_Payable varchar(10) ,
             Bill_Date datetime,
             Currency_Code varchar(10),
             Paid_Amt decimal(13,2),
             WriteOff_Amt decimal(13,2),
             Money_Type   varchar(10),
             SourceBill_No varchar(30),
             CurrWriteOf_Amt decimal(13,2))
             
Insert into #Tmp_Payment
Select '7301-20150915001-0001','S235','2015/09/15','RMB',1200,1200,1,'7A01-20150915001',2100 UNION ALL
Select '7301-20150915001-0002','S235','2015/09/15','RMB',1200,1200,4,'7A01-20150915001',-400 UNION ALL
Select '7301-20150915001-0003','S235','2015/09/15','RMB',1200,1200,4,'7A01-20150915002',-500



DECLARE @Payment_Desc varchar(1000)
SET @Payment_Desc=''
SELECT @Payment_Desc=@Payment_Desc+Money_Type+','+ SourceBill_No+char(13) From #Tmp_Payment Where Taget_Payable='S235'

Select min(Payment_No)as Payment_No,Taget_Payable,Bill_Date,Currency_Code,max(paid_amt) as paid_amt,max(writeoff_amt) as WirteOff_Amt,
       @Payment_Desc as Payment_Desc,sum(CurrWriteOf_Amt) as CurrWriteOf_Amt
From #Tmp_Payment   
Group By Taget_Payable,Bill_Date,Currency_Code

#6

if object_id('tempdb..#Tmp_Payment') is not null
drop table #Tmp_Payment

CREATE TABLE #Tmp_Payment (
             Payment_No  varchar(30) ,
             Taget_Payable varchar(10) ,
             Bill_Date datetime,
             Currency_Code varchar(10),
             Paid_Amt decimal(13,2),
             WriteOff_Amt decimal(13,2),
             Money_Type   varchar(10),
             SourceBill_No varchar(30),
             CurrWriteOf_Amt decimal(13,2))
             
Insert into #Tmp_Payment
Select '7301-20150915001-0001','S235','2015/09/15','RMB',1200,1200,1,'7A01-20150915001',2100 UNION ALL
Select '7301-20150915001-0002','S235','2015/09/15','RMB',1200,1200,4,'7A01-20150915001',-400 UNION ALL
Select '7301-20150915001-0003','S235','2015/09/15','RMB',1200,1200,4,'7A01-20150915002',-500



DECLARE @Payment_Desc varchar(1000)
SET @Payment_Desc=''
SELECT @Payment_Desc=@Payment_Desc+Money_Type+','+ SourceBill_No+char(13) From #Tmp_Payment Where Taget_Payable='S235'

Select min(Payment_No)as Payment_No,Taget_Payable,Bill_Date,Currency_Code,max(paid_amt) as paid_amt,max(writeoff_amt) as WirteOff_Amt,
       @Payment_Desc as Payment_Desc,sum(CurrWriteOf_Amt) as CurrWriteOf_Amt
From #Tmp_Payment   
Group By Taget_Payable,Bill_Date,Currency_Code