STRING_SPLIT in TSQL on MS Server creates list with repeating values. WHY?

I am using 2017 MS SQL Express. The following code creates this erroneous result. Any clues why?

SELECT BNAME, LIC_STRING INTO dbo.SEPARATE2
FROM dbo.together
CROSS APPLY STRING_SPLIT(LIC_STRING, ',');  


table dbo.together                         result, dbo.SEPARATE2

BNAME | LIC_STRING                         BNAME | LIC_STRING

A1    | AB,AC,AD                             A1  | AB,AC,AD
B2    | AX,AD                                A1  | AB,AC,AD
                                             A1  | AB,AC,AD
                                             B2  | AX,AD
                                             B2  | AX,AD

Answer

You are using cross apply which is applying your join on 2 tables where result contain table on left with all the columns and table on right with matching values.

Since in this query dbo.together has 2 rows and for each value in string_split it generate same number of rows. 1:n relation in between these 2 tables.

SELECT TOGETHER.BNAME, STR.VALUE INTO dbo.SEPARATE2
FROM dbo.together AS TOGETHER
CROSS APPLY (SELECT VALUE FROM STRING_SPLIT(TOGETHER.LIC_STRING, ',')) AS STR; 

You may find this link for more details on CROSS APPLY.