怎么把相同分组的其他列组合起来？

[yanele]

求助大神，怎么将相同分组的其他列组合起来？
有这样一组原始数据：
单号 序号 影响因素 影响原因 部门意见
2021010701 001 材料影响
2021010701 001 料 工装影响
2021010701 001 人 缺人员
2021010702 001 材料影响
2021010702 002 料 无坯件

当单号与序号相同时，将后面三列的数据组合起来，且每个字段用符号间隔开，空信息就不用符号，已存在或重复的不信息不叠加：
单号 序号 影响因素 影响原因 部门意见 汇总影响信息
2021010701 001 料 | 人 工装影响 | 缺人员 材料影响 料 | 人 + 工装影响 | 缺人员 + 材料影响
2021010702 001 材料影响 材料影响
2021010702 002 料 无坯件 料 + 无坯件

[Hello World,]

用STRING_AGG函数

[锟斤拷锟斤拷]

CREATE TABLE #A(
ORDERID INT,
ID INT,
FACTORS VARCHAR(20),
CAUSES VARCHAR(20),
DEPT_REMARK VARCHAR(20)
)
INSERT INTO #A VALUES (2021010701,1,'','','材料影响')
INSERT INTO #A VALUES (2021010701,1,'料','工装影响','')
INSERT INTO #A VALUES (2021010701,1,'人','缺人员','')
INSERT INTO #A VALUES (2021010702,1,'','','材料影响')
INSERT INTO #A VALUES (2021010702,2,'料','无坯件','')

SELECT XX.ORDERID,XX.ID,XX.FACTORS,XX.CAUSES,XX.DEPT_REMARK,
ISNULL(
(CASE XX.FACTORS WHEN NULL THEN '' ELSE XX.FACTORS END)+
(CASE XX.CAUSES WHEN NULL THEN '' ELSE '+'+XX.CAUSES END),'')+
ISNULL(XX.DEPT_REMARK,'')
FROM (
SELECT A.ORDERID,A.ID,
STUFF((SELECT (CASE B.FACTORS WHEN '' THEN '' ELSE '/'+B.FACTORS END) FROM #A B WHERE A.ORDERID = B.ORDERID AND A.ID = B.ID FOR XML PATH ('')),1,1,'') FACTORS,
STUFF((SELECT (CASE B.CAUSES WHEN '' THEN '' ELSE '/'+B.CAUSES END) FROM #A B WHERE A.ORDERID = B.ORDERID AND A.ID = B.ID FOR XML PATH ('')),1,1,'') CAUSES,
STUFF((SELECT (CASE B.DEPT_REMARK WHEN '' THEN '' ELSE '/'+B.DEPT_REMARK END) FROM #A B WHERE A.ORDERID = B.ORDERID AND A.ID = B.ID FOR XML PATH ('')),1,1,'') DEPT_REMARK
FROM #A A GROUP BY A.ORDERID,A.ID)XX

DROP TABLE #A

[yanele]

重复的不信息不叠加，怎么处理？
单号 序号 影响因素 影响原因 部门意见
2021010701 001 材料影响
2021010701 001 料 工装影响
2021010701 001 人 缺人员
2021010701 001 料 采购缺货

第3列的第2行与第4行‘料’重复的，影响因素能否实现不重复？
单号 序号 影响因素 影响原因 部门意见 汇总影响信息
2021010701 001 料 | 人 工装影响 | 缺人员 | 采购缺货 材料影响 料 | 人 + 工装影响 | 缺人员 | 采购缺货 + 材料影响

[锟斤拷锟斤拷]

加distinct

CREATE TABLE #A(
ORDERID INT,
ID INT,
FACTORS VARCHAR(20),
CAUSES VARCHAR(20),
DEPT_REMARK VARCHAR(20)
)
INSERT INTO #A VALUES (2021010701,1,'','','材料影响')
INSERT INTO #A VALUES (2021010701,1,'料','工装影响','')
INSERT INTO #A VALUES (2021010701,1,'人','缺人员','')
INSERT INTO #A VALUES (2021010702,1,'','','材料影响')
INSERT INTO #A VALUES (2021010701,1,'料','无坯件','')
 
SELECT XX.ORDERID,XX.ID,XX.FACTORS,XX.CAUSES,XX.DEPT_REMARK,
ISNULL(
(CASE XX.FACTORS WHEN NULL THEN '' ELSE XX.FACTORS END)+
(CASE XX.CAUSES WHEN NULL THEN '' ELSE '+'+XX.CAUSES END),'')+
ISNULL(XX.DEPT_REMARK,'')
FROM (
SELECT A.ORDERID,A.ID,
STUFF((SELECT DISTINCT (CASE B.FACTORS WHEN '' THEN '' ELSE '/'+B.FACTORS END) FROM #A B WHERE A.ORDERID = B.ORDERID AND A.ID = B.ID FOR XML PATH ('')),1,1,'') FACTORS,
STUFF((SELECT DISTINCT (CASE B.CAUSES WHEN '' THEN '' ELSE '/'+B.CAUSES END) FROM #A B WHERE A.ORDERID = B.ORDERID AND A.ID = B.ID FOR XML PATH ('')),1,1,'') CAUSES,
STUFF((SELECT DISTINCT (CASE B.DEPT_REMARK WHEN '' THEN '' ELSE '/'+B.DEPT_REMARK END) FROM #A B WHERE A.ORDERID = B.ORDERID AND A.ID = B.ID FOR XML PATH ('')),1,1,'') DEPT_REMARK
FROM #A A GROUP BY A.ORDERID,A.ID)XX
 
DROP TABLE #A

[RINK_1]

借用#2的数据

CREATE TABLE #A(
ORDERID INT,
ID INT,
FACTORS VARCHAR(20),
CAUSES VARCHAR(20),
DEPT_REMARK VARCHAR(20)
)
INSERT INTO #A VALUES (2021010701,1,'','','材料影响')
INSERT INTO #A VALUES (2021010701,1,'料','工装影响','')
INSERT INTO #A VALUES (2021010701,1,'人','材料影响','')
INSERT INTO #A VALUES (2021010702,1,'','','材料影响')
INSERT INTO #A VALUES (2021010702,2,'料','无坯件','')

SELECT *,ISNULL(FACTORS+'+','')+ISNULL(CAUSES+'+','')+ISNULL(DEPT_REMARK+'+','')
FROM 
(SELECT DISTINCT ORDERID,ID,
STUFF((SELECT DISTINCT '|'+FACTORS FROM #A WHERE A.ORDERID=ORDERID AND A.ID=ID AND FACTORS<>'' FOR XML PATH('')),1,1,'') AS FACTORS,
STUFF((SELECT DISTINCT '|'+CAUSES FROM #A WHERE A.ORDERID=ORDERID AND A.ID=ID AND CAUSES<>'' FOR XML PATH('')),1,1,'') AS CAUSES,
STUFF((SELECT DISTINCT '|'+DEPT_REMARK FROM #A WHERE A.ORDERID=ORDERID AND A.ID=ID AND DEPT_REMARK<>'' FOR XML PATH('')),1,1,'') AS DEPT_REMARK
FROM #A A) AS B

[yanele]

太强了，但刚才换成我的数据，有10000多行，效率有些慢，差不多在2分钟才出结果。