SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE DetailTable
	(A1 nvarchar(20), A2 nvarchar(20),A3 nvarchar(200));
INSERT INTO DetailTable SELECT A1 = N'0522',A2 = N'NT$64851.98' ,
A3 = N'{投組入帳明細}&#13 StrA1 &#13 StrA2 &#13 '
INSERT INTO DetailTable SELECT A1 = N'0522',A2 = N'NT$64851.98' ,
A3 = N'{投組入帳明細}&#13 StrB1 &#13 StrB2 &#13 '
INSERT INTO DetailTable SELECT A1 = N'0522',A2 = N'NT$64851.98' ,
A3 = N'{投組入帳明細}&#13 StrC1 &#13 StrC2 &#13 '
INSERT INTO DetailTable SELECT A1 = N'0522',A2 = N'NT$64851.98' ,
A3 = N'{投組入帳明細}&#13 StrD1 &#13 StrD2 &#13 '
4 rows affected

INSERT INTO DetailTable SELECT A1 = N'0523',A2 = N'NT$99999.99' ,
A3 = N'{投組入帳明細}&#13 StrE1 &#13 StrE2 &#13 '
INSERT INTO DetailTable SELECT A1 = N'0523',A2 = N'NT$99999.99' ,
A3 = N'{投組入帳明細}&#13 StrF1 &#13 StrF2 &#13 '
INSERT INTO DetailTable SELECT A1 = N'0523',A2 = N'NT$99999.99' ,
A3 = N'{投組入帳明細}&#13 StrG1 &#13 StrG2 &#13 '
INSERT INTO DetailTable SELECT A1 = N'0523',A2 = N'NT$99999.99' ,
A3 = N'{投組入帳明細}&#13 StrH1 &#13 StrH2 &#13 '
4 rows affected

SELECT *
FROM DetailTable;


A1
A2
A3


0522
NT$64851.98
{投組入帳明細}&#13 StrA1 &#13 StrA2 &#13

0522
NT$64851.98
{投組入帳明細}&#13 StrB1 &#13 StrB2 &#13

0522
NT$64851.98
{投組入帳明細}&#13 StrC1 &#13 StrC2 &#13

0522
NT$64851.98
{投組入帳明細}&#13 StrD1 &#13 StrD2 &#13

0523
NT$99999.99
{投組入帳明細}&#13 StrE1 &#13 StrE2 &#13

0523
NT$99999.99
{投組入帳明細}&#13 StrF1 &#13 StrF2 &#13

0523
NT$99999.99
{投組入帳明細}&#13 StrG1 &#13 StrG2 &#13

0523
NT$99999.99
{投組入帳明細}&#13 StrH1 &#13 StrH2 &#13


SELECT DISTINCT A1,A2,
N'{投組入帳明細}'+'&#13'+REPLACE(LEFT(A3New,len(A3New)),',','&#13') AS A3NewComb
from (
SELECT A1,A2,(SELECT CAST(REPLACE(REPLACE(A3+',','&#13',','),N'{投組入帳明細},','') AS NVARCHAR ) + '' from DetailTable TableQ
WHERE TableQ.A1 = TableP.A1 AND TableQ.A2=TableP.A2
FOR XML PATH('')) AS A3New
FROM DetailTable TableP
WHERE 1=1
) AS TableR
WHERE 1=1
ORDER BY A1,A2


A1
A2
A3NewComb


0522
NT$64851.98
{投組入帳明細}&#13 StrA1 &#13 StrA2 &#13 &#13 StrB1 &#13 StrB2 &#13 &#13 StrC1 &#13 StrC2 &#13 &#13 StrD1 &#13 StrD2 &#13 &#13

0523
NT$99999.99
{投組入帳明細}&#13 StrE1 &#13 StrE2 &#13 &#13 StrF1 &#13 StrF2 &#13 &#13 StrG1 &#13 StrG2 &#13 &#13 StrH1 &#13 StrH2 &#13 &#13


SELECT DISTINCT A1,A2,
N'{投組入帳明細}'+'&#13'+REPLACE(LEFT(A3New,len(A3New)),',','&#13') AS A3NewComb
from (
SELECT A1,A2,(SELECT CAST(REPLACE(REPLACE(A3+',','&#13',','),N'{投組入帳明細},','') AS NVARCHAR ) + '' from DetailTable TableQ
WHERE TableQ.A1 = TableP.A1 AND TableQ.A2=TableP.A2
FOR XML PATH('')) AS A3New
FROM DetailTable TableP
WHERE 1=1
) AS TableR
WHERE 1=1
-- 0522 Only
AND A1='0522'
ORDER BY A1,A2


A1
A2
A3NewComb


0522
NT$64851.98
{投組入帳明細}&#13 StrA1 &#13 StrA2 &#13 &#13 StrB1 &#13 StrB2 &#13 &#13 StrC1 &#13 StrC2 &#13 &#13 StrD1 &#13 StrD2 &#13 &#13

A1	A2	A3
0522	NT$64851.98	{投組入帳明細}&#13 StrA1 &#13 StrA2 &#13
0522	NT$64851.98	{投組入帳明細}&#13 StrB1 &#13 StrB2 &#13
0522	NT$64851.98	{投組入帳明細}&#13 StrC1 &#13 StrC2 &#13
0522	NT$64851.98	{投組入帳明細}&#13 StrD1 &#13 StrD2 &#13
0523	NT$99999.99	{投組入帳明細}&#13 StrE1 &#13 StrE2 &#13
0523	NT$99999.99	{投組入帳明細}&#13 StrF1 &#13 StrF2 &#13
0523	NT$99999.99	{投組入帳明細}&#13 StrG1 &#13 StrG2 &#13
0523	NT$99999.99	{投組入帳明細}&#13 StrH1 &#13 StrH2 &#13

A1	A2	A3NewComb
0522	NT$64851.98	{投組入帳明細}&#13 StrA1 &#13 StrA2 &#13 &#13 StrB1 &#13 StrB2 &#13 &#13 StrC1 &#13 StrC2 &#13 &#13 StrD1 &#13 StrD2 &#13 &#13
0523	NT$99999.99	{投組入帳明細}&#13 StrE1 &#13 StrE2 &#13 &#13 StrF1 &#13 StrF2 &#13 &#13 StrG1 &#13 StrG2 &#13 &#13 StrH1 &#13 StrH2 &#13 &#13