四时宝库

程序员的知识宝库

2024年09月27日

SQL Server中将多行数据合并为一行，每个值之间用逗号分隔

一、函数功能：

stuff(select ',' + fieldname from tablename for xml path('')),1,1,'')

这段代码是在SQL Server中使用的，用于将多行数据合并为一行，每个值之间用逗号分隔。

具体来说，stuff() 函数用于替换字符串中的一部分。select ',' + fieldname from tablename for xml path('') 部分会从 tablename 表中选择 fieldname 字段，并为每行添加一个逗号，然后通过 for xml path('') 将结果转换为XML格式的字符串。最后，stuff() 函数将这个字符串的第一个字符替换为空字符串，从而移除开头的逗号。

二、语法:

stuff(select '分隔符' + 字段名 from 表名 for xml path('')),起始位置大于等于1,移除字符个数,'')

stuff(select ',' + fieldname from tablename for xml path('')),1,1,'') 这一整句的作用是将多行fieldname字段的内容串联起来，用逗号分隔。

三、实现效果：

fieldname

-----------

AAA

BBB

CCC

串联之后就是字符串： AAA,BBB,CCC


示例1：
--Table_Main--
BillNo
001
002

--Table_Detail--
BillNo　　 DetailNo
001 　　　　123
001 　　　　456
001 　　　　789

SELECT '；'+RTRIM(DetailNo) FROM Table_Detail FOR XML PATH('')

SELECT (SELECT '；'+RTRIM(DetailNo) FROM Table_Detail FOR XML PATH(''))

SELECT STUFF((SELECT '；'+RTRIM(DetailNo) FROM Table_Detail FOR XML PATH('')),1,1,'')

SELECT A.BillNo, STUFF((SELECT '；'+RTRIM(DetailNo) FROM table_detail CO WHERE A.BillNo = CO.BillNo FOR XML PATH('')),1,1,'') DetailNo FROM TABLE_Main A

示例2：

示例为工作中的应用，通过子查询方式，实现订单销货/销退销售发票全流程综合报表中核销批号和核销价格的查询和呈现（一张销售发票可能对应多次收款情况）。

---订单销货/销退销售发票全流程综合报表

---定义客户和日期变量

declare @kehu varchar(20)

declare @dtm datetime

set @kehu='X001'

set @dtm ='20210831'

--1、订单销货开票综合报表

SELECT DISTINCT TC004 客户编号, COPMA.MA002 客户简称, substring(COPTC.TC003,1,4)+'-'+substring(COPTC.TC003,5,2)订单年月, TD001+'-'+TD002+'-'+TD003 订单单号,

COPTD.UDF01 订单令号, TD004 品号,TD005 品名,

substring (TD013,1,4)+'-'+substring (TD013,5,2)+'-'+substring (TD013,7,2) 预交货日,TD008 订单数量,TD009 已交数量, CCOPMA.MA002 销货客户,

substring(TG003,1,4)+'-'+substring(TG003,5,2)+'-'+substring(TG003,7,2)销货日期,

RTRIM(TH001)+'-'+RTRIM(TH002)+'-'+RTRIM(TH003) 销货单号, TH020 销货审核码,

COPTH.UDF01 订单令号2,COPTH.TH004 品号2,COPTH.TH005 品名2,TH009 单位, TH008 销货数量,COPTD.TD901 合同单价 ,COPTD.TD901*TH008 合同金额,TH026 开票码,substring(TA038,1,4)+'-'+substring(TA038,5,2)+'-'+substring(TA038,7,2)发票单据日期,

Rtrim(TA001)+'-'+Rtrim(TA002)+'-'+Rtrim(TB003) 销售发票单号,ACRTA.UDF01 合同号, ACRTA.TA025 发票审核码,

(CASE WHEN TA079='1'THEN'蓝字'

WHEN TA079='2'THEN'红字' END) 单据类型 ,TA004 发票客户编号, BCOPMA.MA002 发票客户简称,RTRIM(DCOPMA.MA002) 发票销货客简, TA022 发票号,

substring(TA016,1,4)+'-'+substring(TA016,5,2)+'-'+substring(TA016,7,2) 开票日期,

TA083 红冲, (TA085+'-'+TA086)红冲发票单号,

(CASE WHEN TA079='1'THEN 1*(TA041+TA042)

WHEN TA079='2'THEN -1*(TA041+TA042) END) 单头本币价税合计,

(CASE WHEN TA100='1'THEN '未核销'

WHEN TA100='2'THEN '部分核销'

WHEN TA100='3'THEN '已核销'

ELSE '' END)核销状态,

(select STUFF((select ';'+B.LC002

FROM ACRLC B

WHERE ACRTA.TA001=B.LC003 AND ACRTA.TA002=B.LC004 and (ACRTA.TA097 = @kehu) and ACRTA.TA003 <=@dtm

FOR XML path('')),1,1,''))核销批号,

(select STUFF((select ';'+CONVERT(varchar(50),B.LC016)

FROM ACRLC B

WHERE ACRTA.TA001=B.LC003 AND ACRTA.TA002=B.LC004 and (ACRTA.TA097 = @kehu) and ACRTA.TA003 <=@dtm

FOR XML path('')),1,1,''))核销价格,

(CASE WHEN TA079='1'THEN 1*TA098

WHEN TA079='2'THEN -1*TA098 END)单头本币已核销金额, (CASE WHEN TB004='1' THEN '销货'

WHEN TB004='2' THEN '销退'

WHEN TB004='5' THEN '冲减预开发票'

WHEN TB004='6' THEN '订单'

WHEN TB004='9' THEN '其他' END)来源, ACRTB.UDF01 订单令号3,ACRTB.TB039 品号3,ACRTB.TB040 品名3,

(CASE WHEN TA079='1'THEN 1*TB022

WHEN TA079='2'THEN -1*TB022 END)开票数量,

TB023 开票单价,

(CASE WHEN TA079='1'THEN 1*TB019

WHEN TA079='2'THEN -1*TB019 END)本币无税金额,

(CASE WHEN TA079='1'THEN 1*TB020

WHEN TA079='2'THEN -1*TB020 END)本币税额,

(CASE WHEN TA079='1'THEN 1*(TB019+TB020)

WHEN TA079='2'THEN -1*(TB019+TB020) END) 本币价税合计,

(CASE WHEN TA079='1' and TA100='3' THEN 1*(TB019+TB020) ----蓝字票且已核销

WHEN TA079='1' and TA100='2' THEN 1*(TA098/(TA041+TA042))*(TB019+TB020) ----蓝字票且部分核销

WHEN TA079='1' and TA100='1' THEN 0 ----蓝字票且未核销

WHEN TA079='2' and TA100='3' THEN -1*(TB019+TB020) ----红字票且已核销

WHEN TA079='2' and TA100='2' THEN -1*(TA098/(TA041+TA042))*(TB019+TB020) ----红字票且部分核销

WHEN TA079='2' and TA100='1' THEN 0 ----红字票且未核销

else null END) 单身本币核销金额

FROM COPTD AS COPTD

LEFT JOIN COPTC AS COPTC ON TC001=TD001 AND TC002=TD002

LEFT JOIN COPTH AS COPTH ON TD001=TH014 AND TD002=TH015 AND TD003=TH016

LEFT JOIN COPTG AS COPTG ON TG001=TH001 AND TG002=TH002

LEFT JOIN COPMA AS COPMA ON TC004=COPMA.MA001

LEFT JOIN COPMA AS CCOPMA ON TC004=CCOPMA.MA001

LEFT JOIN ACRTB AS ACRTB ON TB005=TH001 AND TB006=TH002 AND TB007=TH003 AND TB004='1'

LEFT JOIN ACRTA AS ACRTA ON TA001=TB001 AND TA002=TB002

LEFT JOIN COPMA AS BCOPMA ON TA004=BCOPMA.MA001

LEFT JOIN COPMA AS DCOPMA ON TA097=DCOPMA.MA001

WHERE (TC004 = @kehu) and (TC039 <= @dtm )

UNION ALL

---2订单销退开票综合报表

SELECT distinct TC004 客户编号, COPMA.MA002 客户简称, substring(COPTC.TC003,1,4)+'-'+substring(COPTC.TC003,5,2)订单年月, TD001+'-'+TD002+'-'+TD003 订单单号,

COPTD.UDF01 订单令号, TD004 品号,TD005 品名, substring (TD013,1,4)+'-'+substring (TD013,5,2)+'-'+substring (TD013,7,2) 预交货日,

TD008 订单数量,TD009 已交数量, CCOPMA.MA002 销货客户,

substring(TI003,1,4)+'-'+substring(TI003,5,2)+'-'+substring(TI003,7,2) 销货日期,RTRIM(TJ001)+'-'+RTRIM(TJ002)+'-'+RTRIM(TJ003) 销货单号,TJ021 销售审核码,

COPTJ.UDF01 订单令号2,COPTJ.TJ004 品号2,COPTJ.TJ005 品名2 , TJ008 单位,-TJ007 销货数量,COPTD.TD901 合同单价, -COPTD.TD901*TJ007 合同金额 ,TJ024 开票码,substring(TA038,1,4)+'-'+substring(TA038,5,2)+'-'+substring(TA038,7,2)发票单据日期,

Rtrim(TA001)+'-'+Rtrim(TA002)+'-'+Rtrim(TB003) 销售发票单号,ACRTA.UDF01 合同号, ACRTA.TA025 发票审核码,

(CASE WHEN TA079='1'THEN'蓝字'

WHEN TA079='2'THEN'红字' END) 单据类型 ,TA004 发票客户编号, BCOPMA.MA002 发票客户简称,RTRIM(DCOPMA.MA002) 发票销货客简, TA022 发票号,

substring(TA016,1,4)+'-'+substring(TA016,5,2)+'-'+substring(TA016,7,2) 开票日期,

TA083 红冲, (TA085+'-'+TA086)红冲发票单号,

(CASE WHEN TA079='1'THEN 1*(TA041+TA042)

WHEN TA079='2'THEN -1*(TA041+TA042) END) 单头本币价税合计,

(CASE WHEN TA100='1'THEN '未核销'

WHEN TA100='2'THEN '部分核销'

WHEN TA100='3'THEN '已核销'

ELSE '' END)核销状态,

(select STUFF((select ';'+B.LC002

FROM ACRLC B

WHERE ACRTA.TA001=B.LC003 AND ACRTA.TA002=B.LC004 and (ACRTA.TA097 = @kehu) and ACRTA.TA003 <=@dtm

FOR XML path('')),1,1,''))核销批号,

(select STUFF((select ';'+CONVERT(varchar(50),B.LC016)

FROM ACRLC B

WHERE ACRTA.TA001=B.LC003 AND ACRTA.TA002=B.LC004 and (ACRTA.TA097 = @kehu) and ACRTA.TA003 <=@dtm

FOR XML path('')),1,1,''))核销价格,

(CASE WHEN TA079='1'THEN 1*TA098

WHEN TA079='2'THEN -1*TA098 END)单头本币已核销金额, (CASE WHEN TB004='1' THEN '销货'

WHEN TB004='2' THEN '销退'

WHEN TB004='5' THEN '冲减预开发票'

WHEN TB004='6' THEN '订单'

WHEN TB004='9' THEN '其他' END)来源, ACRTB.UDF01 订单令号3,ACRTB.TB039 品号3,ACRTB.TB040 品名3,

(CASE WHEN TA079='1'THEN 1*TB022

WHEN TA079='2'THEN -1*TB022 END)开票数量,

TB023 开票单价,

(CASE WHEN TA079='1'THEN 1*TB019

WHEN TA079='2'THEN -1*TB019 END)本币无税金额,

(CASE WHEN TA079='1'THEN 1*TB020

WHEN TA079='2'THEN -1*TB020 END)本币税额,

(CASE WHEN TA079='1'THEN 1*(TB019+TB020)

WHEN TA079='2'THEN -1*(TB019+TB020) END) 本币价税合计,

(CASE WHEN TA079='1' and TA100='3' THEN 1*(TB019+TB020) ----蓝字票且已核销

WHEN TA079='1' and TA100='2' THEN 1*(TA098/(TA041+TA042))*(TB019+TB020) ----蓝字票且部分核销

WHEN TA079='1' and TA100='1' THEN 0 ----蓝字票且未核销

WHEN TA079='2' and TA100='3' THEN -1*(TB019+TB020) ----红字票且已核销

WHEN TA079='2' and TA100='2' THEN -1*(TA098/(TA041+TA042))*(TB019+TB020) ----红字票且部分核销

WHEN TA079='2' and TA100='1' THEN 0 ----红字票且未核销

else null END) 单身本币核销金额

FROM COPTD AS COPTD

LEFT JOIN COPTC AS COPTC ON TC001=TD001 AND TC002=TD002

LEFT JOIN COPTJ AS COPTJ ON TD001=TJ018 AND TD002=TJ019 AND TD003=TJ020

inner JOIN COPTI AS COPTI ON TI001=TJ001 AND TI002=TJ002

LEFT JOIN COPMA AS COPMA ON TC004=COPMA.MA001

LEFT JOIN COPMA AS CCOPMA ON TC004=CCOPMA.MA001

LEFT JOIN ACRTB AS ACRTB ON TB005=TJ001 AND TB006=TJ002 AND TB007=TJ003 AND TB004='2'

LEFT JOIN ACRTA AS ACRTA ON TA001=TB001 AND TA002=TB002

LEFT JOIN COPMA AS BCOPMA ON TA004=BCOPMA.MA001

LEFT JOIN COPMA AS DCOPMA ON TA097=DCOPMA.MA001

WHERE (TC004 = @kehu) and (TC039 <= @dtm )

UNION ALL

--3、销售发票单身来源 冲减预开发票、订单、其他

SELECT distinct TC004 客户编号, COPMA.MA002 客户简称, substring(COPTC.TC003,1,4)+'-'+substring(COPTC.TC003,5,2)订单年月, TD001+'-'+TD002+'-'+TD003 订单单号,

COPTD.UDF01 订单令号, TD004 品号,TD005 品名, substring (TD013,1,4)+'-'+substring (TD013,5,2)+'-'+substring (TD013,7,2) 预交货日,

TD008 订单数量,TD009 已交数量, CCOPMA.MA002 销货客户,

substring(TI003,1,4)+'-'+substring(TI003,5,2)+'-'+substring(TI003,7,2) 销货日期,RTRIM(TJ001)+'-'+RTRIM(TJ002)+'-'+RTRIM(TJ003) 销货单号, TH020 销货审核码,

COPTH.UDF01 订单令号2,COPTH.TH004 品号2,COPTH.TH005 品名2, TJ008 单位,-TJ007 销货数量,COPTD.TD901 合同单价, -COPTD.TD901*TJ007 合同金额 ,TJ024 开票码,

substring(TA038,1,4)+'-'+substring(TA038,5,2)+'-'+substring(TA038,7,2)发票单据日期,

Rtrim(TA001)+'-'+Rtrim(TA002)+'-'+Rtrim(TB003) 销售发票单号,ACRTA.UDF01 合同号, ACRTA.TA025 发票审核码,

(CASE WHEN TA079='1'THEN'蓝字'

WHEN TA079='2'THEN'红字' END) 单据类型 ,TA004 发票客户编号, BCOPMA.MA002 发票客户简称,RTRIM(DCOPMA.MA002) 发票销货客简, TA022 发票号,

substring(TA016,1,4)+'-'+substring(TA016,5,2)+'-'+substring(TA016,7,2) 开票日期,

TA083 红冲, (TA085+'-'+TA086)红冲发票单号,

(CASE WHEN TA079='1'THEN 1*(TA041+TA042)

WHEN TA079='2'THEN -1*(TA041+TA042) END) 单头本币价税合计,

(CASE WHEN TA100='1'THEN '未核销'

WHEN TA100='2'THEN '部分核销'

WHEN TA100='3'THEN '已核销'

ELSE '' END)核销状态,

(select STUFF((select ';'+B.LC002

FROM ACRLC B

WHERE ACRTA.TA001=B.LC003 AND ACRTA.TA002=B.LC004 and (ACRTA.TA097 = @kehu) and ACRTA.TA003 <=@dtm

FOR XML path('')),1,1,''))核销批号,

(select STUFF((select ';'+CONVERT(varchar(50),B.LC016)

FROM ACRLC B

WHERE ACRTA.TA001=B.LC003 AND ACRTA.TA002=B.LC004 and (ACRTA.TA097 = @kehu) and ACRTA.TA003 <=@dtm

FOR XML path('')),1,1,''))核销价格,

(CASE WHEN TA079='1'THEN 1*TA098

WHEN TA079='2'THEN -1*TA098 END)单头本币已核销金额, (CASE WHEN TB004='1' THEN '销货'

WHEN TB004='2' THEN '销退'

WHEN TB004='5' THEN '冲减预开发票'

WHEN TB004='6' THEN '订单'

WHEN TB004='9' THEN '其他' END)来源, ACRTB.UDF01 订单令号3,ACRTB.TB039 品号3,ACRTB.TB040 品名3,

(CASE WHEN TA079='1'THEN 1*TB022

WHEN TA079='2'THEN -1*TB022 END)开票数量,

TB023 开票单价,

(CASE WHEN TA079='1'THEN 1*TB019

WHEN TA079='2'THEN -1*TB019 END)本币无税金额,

(CASE WHEN TA079='1'THEN 1*TB020

WHEN TA079='2'THEN -1*TB020 END)本币税额,

(CASE WHEN TA079='1'THEN 1*(TB019+TB020)

WHEN TA079='2'THEN -1*(TB019+TB020) END) 本币价税合计,

(CASE WHEN TA079='1' and TA100='3' THEN 1*(TB019+TB020) ----蓝字票且已核销

WHEN TA079='1' and TA100='2' THEN 1*(TA098/(TA041+TA042))*(TB019+TB020) ----蓝字票且部分核销

WHEN TA079='1' and TA100='1' THEN 0 ----蓝字票且未核销

WHEN TA079='2' and TA100='3' THEN -1*(TB019+TB020) ----红字票且已核销

WHEN TA079='2' and TA100='2' THEN -1*(TA098/(TA041+TA042))*(TB019+TB020) ----红字票且部分核销

WHEN TA079='2' and TA100='1' THEN 0 ----红字票且未核销

else null END) 单身本币核销金额

FROM ACRTB AS ACRTB

LEFT JOIN ACRTA AS ACRTA ON TA001=TB001 AND TA002=TB002

LEFT JOIN COPTH AS COPTH ON TB005=TH001 AND TB006=TH002 AND TB007=TH003 AND TB004='1'

LEFT JOIN COPTG AS COPTG ON COPTG.TG001=TB005 AND COPTG.TG002=TB006 AND TB004='1'

LEFT JOIN COPTJ AS COPTJ ON TB005=TJ001 AND TB006=TJ002 AND TB007=TJ003 AND TB004='2'

LEFT JOIN COPTD AS COPTD ON TD001=TJ018 AND TD002=TJ019 AND TD003=TJ020

LEFT JOIN COPTC AS COPTC ON TC001=TD001 AND TC002=TD002

LEFT JOIN COPTI AS COPTI ON TI001=TJ001 AND TI002=TJ002

LEFT JOIN COPMA AS COPMA ON TC004=COPMA.MA001

LEFT JOIN COPMA AS BCOPMA ON TA004=BCOPMA.MA001

LEFT JOIN COPMA AS DCOPMA ON TA097=DCOPMA.MA001

LEFT JOIN COPMA AS CCOPMA ON TG004=CCOPMA.MA001

WHERE (ACRTA.TA001 <> '6100') and (TB004 = '5' OR TB004 = '6' OR TB004 = '9')

and (ACRTA.TA097 = @kehu) and TA003 <=@dtm

ORDER BY TC004, TD001+'-'+TD002+'-'+TD003

四、扩展：

mysql数据库实现方式 select group_concat(DetailNo) from table_detail

标签：sqlserver字符串替换 

作者:sishi88 , 分类:技术教程 , 浏览:8 , 评论:0

发表评论:

控制面板
您好，欢迎到访网站！
  查看权限
网站分类
最新留言
    友情链接