T-SQL Split colum-values in SELECT-Statement without CURSOR-CodePudding

I need some food for thought from you guys for some tricky problem.

I have a table with the following structure:

1, "word1 word2 word3"
2, "word4 word5 word6"

DECLARE @input TABLE(id INT, words NVARCHAR(max))
INSERT INTO @input (id, words) VALUES 
(1, 'word1 word2 word3')
, (2, 'word4 word5 word6')

and I want to flatten this into a table with the following structure:

1, "word1"
1, "word2"
1, "word3"
2, "word4"
2, "word5"
2, "word6"

DECLARE @result TABLE(id INT, word NVARCHAR(50))

I know this could easily be done with a cursor, but it takes a lot of time with a large number of records.

DECLARE @id INT, @words AS NVARCHAR(MAX);

DECLARE allInputRecords CURSOR FAST_FORWARD READ_ONLY FOR 
   SELECT id, words FROM @input I;
OPEN allInputRecords;
FETCH NEXT FROM allInputRecords INTO @id, @words;

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @result (id, word)
    SELECT @id, SS.value FROM STRING_SPLIT(@words, ' ') SS;

    FETCH NEXT FROM allInputRecords INTO @id, @words;
END

Is there a way to do this without a cursor, possibly with Common Table Expressions (CTE) or something similar?

Thanks for all your ideas, Robert

CodePudding user response：

I suppose your problem is that you don't know how to join the result of string_agg to your select? For that you should be using either Cross or Outer Apply like below:

SELECT i.id, sp.value
FROM @input i
CROSS APPLY string_split(words, ' ') sp