Server中带有ID的拆分记录

Question

我有这样的问题。我需要把句子和一列单词分开，就像在表格里一样。该怎么做呢？

--I have table #Keywords like this:
CREATE TABLE #Keywords 
(
    Word nvarchar(400),
    Id int
)

INSERT INTO #Keywords VALUES ('some text1 to spliting', 1);
INSERT INTO #Keywords VALUES ('some text2 to spliting', 2);
INSERT INTO #Keywords VALUES ('some text3 to spliting', 3);

SELECT * FROM #Keywords

-- In result I want table like this:
CREATE TABLE #KeywordsResult 
(
    Word nvarchar(400),
    Id int
)

INSERT INTO #KeywordsResult VALUES ('some', 1);
INSERT INTO #KeywordsResult VALUES ('text1', 1);
INSERT INTO #KeywordsResult VALUES ('to', 1);
INSERT INTO #KeywordsResult VALUES ('spliting', 1);
INSERT INTO #KeywordsResult VALUES ('some', 2);
INSERT INTO #KeywordsResult VALUES ('text2', 2);
INSERT INTO #KeywordsResult VALUES ('to', 2);
INSERT INTO #KeywordsResult VALUES ('spliting', 2);
INSERT INTO #KeywordsResult VALUES ('some', 3);
INSERT INTO #KeywordsResult VALUES ('text3', 3);
INSERT INTO #KeywordsResult VALUES ('to', 3);
INSERT INTO #KeywordsResult VALUES ('spliting', 3);

SELECT * FROM #KeywordsResult

Answer 1

使用string_split是非常容易的：

select * from [#Keywords] k
cross apply string_split(k.[Word], ' ')