Subquery returned more than 1 value when using Union and string

I have a query that will insert data in a table using UNION.

DECLARE @testTable TABLE
(
  ID smallint,
  ACode varchar(64)
)

INSERT INTO @testTable
SELECT 1, 'A1,B2'
UNION ALL
SELECT 2, 'C1,D2'


SELECT [value] As ACode
FROM STRING_SPLIT
(
  (
    SELECT t.ACode
    FROM @testTable t
  ),
  ','
)

I don't encounter problems when there is only a single row that I need to return. I am expecting that when there is two or more UNION it will just append at the end, but it gives me an error. "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

Can anyone help me to fix my query?

I am expecting something like this :

DEMO SQL

CodePudding user response：

Use a CROSS APPLY instead

Example

Select ACode=B.value
 From  @testTable A
 Cross Apply string_split(ACode,',') B

Results

ACode
A1
B2
C1
D2