I have a column with some Events names.I need to split one of the names into as many rows as words are in that name. Lets say 'The UK declares war on Germany' have to become

1.The
2.UK
3.declares
4.war
5.on
6.Germany

enter image description here

CodePudding user response：

If you want to to use STRING_SPLIT wit a column from anoter table, you need some more code for it

CREATE TABLE test_table(id int, name varchar(100))
GO

INSERT INTO test_table VALUEs (1,'Test text'), (3,'The UK declares war on Germany')
GO

SELECT a.*
FROM test_table  as t
    CROSS APPLY STRING_SPLIT(name, ' ') AS a
 WHERE t.id = 3;  
GO

| value    |
| :------- |
| The      |
| UK       |
| declares |
| war      |
| on       |
| Germany  |

db<>fiddle here