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));
-- 將 StrB ,StrC2 ,StrC1 隨機放置
INSERT INTO DetailTable SELECT A1 = N'0522',A2 = N'NT$64851.98' ,
A3 = N'{投組入帳明細}&#13 StrA &#13 StrA1 &#13 '
INSERT INTO DetailTable SELECT A1 = N'0522',A2 = N'NT$64851.98' ,
A3 = N'{投組入帳明細}&#13 StrC &#13 StrC2 &#13 '
INSERT INTO DetailTable SELECT A1 = N'0522',A2 = N'NT$64851.98' ,
A3 = N'{投組入帳明細}&#13 StrC &#13 StrC1 &#13 '
INSERT INTO DetailTable SELECT A1 = N'0522',A2 = N'NT$64851.98' ,
A3 = N'{投組入帳明細}&#13 StrB &#13 StrB1 &#13 '
4 rows affected

INSERT INTO DetailTable SELECT A1 = N'0523',A2 = N'NT$99999.99' ,
A3 = N'{投組入帳明細}&#13 StrD &#13 StrD1 &#13 '
INSERT INTO DetailTable SELECT A1 = N'0523',A2 = N'NT$99999.99' ,
A3 = N'{投組入帳明細}&#13 StrE &#13 StrE1 &#13 '
INSERT INTO DetailTable SELECT A1 = N'0523',A2 = N'NT$99999.99' ,
A3 = N'{投組入帳明細}&#13 StrF &#13 StrF1 &#13 '
INSERT INTO DetailTable SELECT A1 = N'0523',A2 = N'NT$99999.99' ,
A3 = N'{投組入帳明細}&#13 StrF &#13 StrF2 &#13 '
4 rows affected

SELECT *
FROM DetailTable;


A1
A2
A3


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

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

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

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

0523
NT$99999.99
{投組入帳明細}&#13 StrD &#13 StrD1 &#13

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

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

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


-- 拆解字串 FUNCTION
  CREATE FUNCTION INSTR (@str VARCHAR(8000), @substr VARCHAR(255), @start INT, @occurrence INT)
  RETURNS INT
  AS
  BEGIN
	DECLARE @found INT = @occurrence,
			@pos INT = @start;
 
	WHILE 1=1 
	BEGIN
		-- Find the next occurrence
		SET @pos = CHARINDEX(@substr, @str, @pos);
 
		-- Nothing found
		IF @pos IS NULL OR @pos = 0
			RETURN @pos;
 
		-- The required occurrence found
		IF @found = 1
			BREAK;
 
		-- Prepare to find another one occurrence
		SET @found = @found - 1;
		SET @pos = @pos + 1;
	END
 
	RETURN @pos;
  END
-- 將 A3 分拆為 A3_01,A3_02,A3_03
CREATE VIEW [dbo].[View_DetailTable1] AS 
SELECT A1,A2,
substring(REPLACE(A3,'&#13',','),1,
len(substring(REPLACE(A3,'&#13',','),1,dbo.INSTR(REPLACE(A3,'&#13',','),',',1,1)))-1) AS A3_01,

substring(REPLACE(A3,'&#13',','),len(substring(REPLACE(A3,'&#13',','),1,dbo.INSTR(REPLACE(A3,'&#13',','),',',1,1)))+1,
len(substring(REPLACE(A3,'&#13',','),1,dbo.INSTR(REPLACE(A3,'&#13',','),',',1,2)))
-len(substring(REPLACE(A3,'&#13',','),1,dbo.INSTR(REPLACE(A3,'&#13',','),',',1,1)))-1) AS A3_02,

substring(REPLACE(A3,'&#13',','),len(substring(REPLACE(A3,'&#13',','),1,dbo.INSTR(REPLACE(A3,'&#13',','),',',1,2)))+1,
len(substring(REPLACE(A3,'&#13',','),1,dbo.INSTR(REPLACE(A3,'&#13',','),',',1,3)))
-len(substring(REPLACE(A3,'&#13',','),1,dbo.INSTR(REPLACE(A3,'&#13',','),',',1,2)))-1) AS A3_03
FROM DetailTable
SELECT *
FROM View_DetailTable1


A1
A2
A3_01
A3_02
A3_03


0522
NT$64851.98
{投組入帳明細}
 StrA
 StrA1

0522
NT$64851.98
{投組入帳明細}
 StrC
 StrC2

0522
NT$64851.98
{投組入帳明細}
 StrC
 StrC1

0522
NT$64851.98
{投組入帳明細}
 StrB
 StrB1

0523
NT$99999.99
{投組入帳明細}
 StrD
 StrD1

0523
NT$99999.99
{投組入帳明細}
 StrE
 StrE1

0523
NT$99999.99
{投組入帳明細}
 StrF
 StrF1

0523
NT$99999.99
{投組入帳明細}
 StrF
 StrF2


-- 組合A3_03 (A1,A2,A3_01,A3_02 相同)
CREATE VIEW [dbo].[View_DetailTable2] AS 
SELECT DISTINCT A1,A2,A3_01,A3_02,
LEFT(A3New,len(A3New)) AS A3NewComb1
FROM (
SELECT A1,A2,A3_01,A3_02,(SELECT CAST(A3_03+',' AS NVARCHAR ) + '' FROM View_DetailTable1 TableQ
WHERE TableQ.A1 = TableP.A1 AND TableQ.A2=TableP.A2
AND TableQ.A3_01 = TableP.A3_01 AND TableQ.A3_02=TableP.A3_02
ORDER BY A1,A2,A3_01,A3_02,A3_03
FOR XML PATH('')) AS A3New
FROM View_DetailTable1 TableP
WHERE 1=1
) AS TableR
WHERE 1=1
SELECT *
FROM View_DetailTable2


A1
A2
A3_01
A3_02
A3NewComb1


0522
NT$64851.98
{投組入帳明細}
 StrA
 StrA1 ,

0522
NT$64851.98
{投組入帳明細}
 StrB
 StrB1 ,

0522
NT$64851.98
{投組入帳明細}
 StrC
 StrC1 , StrC2 ,

0523
NT$99999.99
{投組入帳明細}
 StrD
 StrD1 ,

0523
NT$99999.99
{投組入帳明細}
 StrE
 StrE1 ,

0523
NT$99999.99
{投組入帳明細}
 StrF
 StrF1 , StrF2 ,


-- 組合A3_02 + A3NewComb1 (A1,A2,A3_01 相同)
SELECT DISTINCT A1,A2,
REPLACE(A3_01+','+LEFT(A3New,len(A3New)),',','&#13') AS A3NewComb2
FROM (
SELECT A1,A2,A3_01,A3_02,(SELECT CAST(A3_02+','+A3NewComb1 AS NVARCHAR ) + ',' FROM View_DetailTable2 TableQ
WHERE TableQ.A1 = TableP.A1 AND TableQ.A2=TableP.A2
AND TableQ.A3_01 = TableP.A3_01 
FOR XML PATH('')) AS A3New
FROM View_DetailTable2 TableP
WHERE 1=1
) AS TableR
WHERE 1=1
-- 0522 Only
AND A1='0522'
ORDER BY A1,A2


A1
A2
A3NewComb2


0522
NT$64851.98
{投組入帳明細}&#13 StrA &#13 StrA1 &#13&#13 StrB &#13 StrB1 &#13&#13 StrC &#13 StrC1 &#13 StrC2 &#13&#13

A1	A2	A3
0522	NT$64851.98	{投組入帳明細}&#13 StrA &#13 StrA1 &#13
0522	NT$64851.98	{投組入帳明細}&#13 StrC &#13 StrC2 &#13
0522	NT$64851.98	{投組入帳明細}&#13 StrC &#13 StrC1 &#13
0522	NT$64851.98	{投組入帳明細}&#13 StrB &#13 StrB1 &#13
0523	NT$99999.99	{投組入帳明細}&#13 StrD &#13 StrD1 &#13
0523	NT$99999.99	{投組入帳明細}&#13 StrE &#13 StrE1 &#13
0523	NT$99999.99	{投組入帳明細}&#13 StrF &#13 StrF1 &#13
0523	NT$99999.99	{投組入帳明細}&#13 StrF &#13 StrF2 &#13

A1	A2	A3_01	A3_02	A3_03
0522	NT$64851.98	{投組入帳明細}	StrA	StrA1
0522	NT$64851.98	{投組入帳明細}	StrC	StrC2
0522	NT$64851.98	{投組入帳明細}	StrC	StrC1
0522	NT$64851.98	{投組入帳明細}	StrB	StrB1
0523	NT$99999.99	{投組入帳明細}	StrD	StrD1
0523	NT$99999.99	{投組入帳明細}	StrE	StrE1
0523	NT$99999.99	{投組入帳明細}	StrF	StrF1
0523	NT$99999.99	{投組入帳明細}	StrF	StrF2