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?
CROSS APPLY instead
Select ACode=B.value From @testTable A Cross Apply string_split(ACode,',') B
ACode A1 B2 C1 D2