SQL Server：将多个字符串分别分成一行

借助XML：

DECLARE @xml xml;WITH cte AS (SELECt *FROM (VALUES(1, 'Today is a good day!'),(2, 'Whatever'),(3, 'Hello my friend')) as t(ID, String))SELECt @xml = (SELECT CAST('<i id="' + CAST(ID as nvarchar(10)) + '"><w>' + REPLACE(REPLACE(String,' ','</w><w>'),'&','&amp;') + '</w></i>' as xml)FROM cteFOR XML PATH(''))SELECt  t.v.value('@id','int') as ID,        t.v.value('w[1]','nvarchar(10)') as String1,        t.v.value('w[2]','nvarchar(10)') as String2,        t.v.value('w[3]','nvarchar(10)') as String3,        t.v.value('w[4]','nvarchar(10)') as String4,        t.v.value('w[5]','nvarchar(10)') as String5,        t.v.value('w[6]','nvarchar(10)') as String6FROM @xml.nodes('/i') as t(v)

输出：

ID          String1    String2    String3    String4    String5    String6----------- ---------- ---------- ---------- ---------- ---------- ----------1Today      is         a          good       day!       NULL2Whatever   NULL       NULL       NULL       NULL       NULL3Hello      my         friend     NULL       NULL       NULL

编辑

与实际表一起使用：

DECLARE @xml xmlSELECt @xml = (SELECT CAST('<i id="' + CAST(ID as nvarchar(10)) + '"><w>' + REPLACE(big_string,' ','</w><w>') + '</w></i>' as xml)FROM [table]FOR XML PATH(''))SELECt  t.v.value('@id','int') as ID,        t.v.value('w[1]','nvarchar(10)') as String1,        t.v.value('w[2]','nvarchar(10)') as String2,        t.v.value('w[3]','nvarchar(10)') as String3,        t.v.value('w[4]','nvarchar(10)') as String4,        t.v.value('w[5]','nvarchar(10)') as String5,        t.v.value('w[6]','nvarchar(10)') as String6,        t.v.value('w[7]','nvarchar(10)') as String7FROM @xml.nodes('/i') as t(v)