最终版本（我希望）：
由于sql server 2008不支持聚合函数的over子句中的order by，因此我添加了另一个cte来添加行索引而不是sum我在之前的版本中使用过：
;WITH cteAllRows as
(
SELECT Item,
ItemIndex,
CASE WHEN ISNUMERIC(Item) = 0 THEN 'String'
WHEN ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) > 0 THEN 'Double'
WHEN ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) = 0 THEN 'Integer'
END As DataType
FROM dbo.SplitStrings_Numbers(@string, ',')
), cteAll as
(
SELECT Item,
DataType,
ItemIndex,
(
SELECT COUNT(*)
FROM cteAllRows tInner
WHERE tInner.DataType = 'String'
AND tInner.ItemIndex <= tOuter.ItemIndex
) As RowIndex
FROM cteAllRows tOuter
)
其余部分与之前的版本相同。
Update
我做的第一件事是将字符串分割函数更改为基于计数表的函数，以便我可以轻松地向其中添加行号。所以，如果你还没有统计表，创建一个 https://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table。
如果您问自己什么是统计表以及为什么需要它，阅读杰夫·莫登的这篇文章 http://www.sqlservercentral.com/articles/T-SQL/62867/:
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO Tally
FROM sys.objects s1
CROSS JOIN sys.objects s2
ALTER TABLE Tally ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)
GO
然后，根据统计表创建字符串分割函数（取自Aaron的文章，但添加了行索引列）：
CREATE FUNCTION dbo.SplitStrings_Numbers
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
SELECT Item = SUBSTRING(@List, Number, CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number),
ROW_NUMBER() OVER (ORDER BY Number) As ItemIndex
FROM dbo.Tally
WHERE Number <= CONVERT(INT, LEN(@List))
AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter
);
GO
现在，我使用的技巧与前一个非常相似，只是现在我在第一个 cte 中添加了一个名为 RowIndex 的新列，这基本上是基于行的字符串计数的运行总计所有行的索引：
SELECT Item,
CASE WHEN ISNUMERIC(Item) = 0 THEN 'String'
WHEN ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) > 0 THEN 'Double'
WHEN ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) = 0 THEN 'Integer'
END As DataType,
SUM(CASE WHEN ISNUMERIC(Item) = 0 THEN 1 END) OVER(ORDER BY ItemIndex) As RowIndex
FROM dbo.SplitStrings_Numbers(@string, ',')
它给了我这个结果：
Item DataType RowIndex
---------- -------- -----------
ddd String 1
1.5 Double 1
1 Integer 1
eee String 2
2.3 Double 2
0 Integer 2
fff String 3
1.2 Double 3
ggg String 4
6.123 Double 4
1 Integer 4
正如您所看到的，我现在每行都有一个数字，所以从现在开始就很简单了：
;WITH cteAll as
(
SELECT Item,
CASE WHEN ISNUMERIC(Item) = 0 THEN 'String'
WHEN ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) > 0 THEN 'Double'
WHEN ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) = 0 THEN 'Integer'
END As DataType,
SUM(CASE WHEN ISNUMERIC(Item) = 0 THEN 1 END) OVER(ORDER BY ItemIndex) As RowIndex
FROM dbo.SplitStrings_Numbers(@string, ',')
), cteString AS
(
SELECT Item, RowIndex
FROM cteAll
WHERE DataType = 'String'
), cteDouble AS
(
SELECT Item, RowIndex
FROM cteAll
WHERE DataType = 'Double'
), cteInteger AS
(
SELECT Item, RowIndex
FROM cteAll
WHERE DataType = 'Integer'
)
SELECT T1.Item As [String],
T2.Item As [Double],
T3.Item As [Integer]
FROM dbo.Tally
LEFT JOIN cteString T1 ON T1.RowIndex = Number
LEFT JOIN cteDouble T2 ON t2.RowIndex = Number
LEFT JOIN cteInteger T3 ON t3.RowIndex = Number
WHERE COALESCE(T1.Item, T2.Item, T3.Item) IS NOT NULL
这给了我这个结果：
String Double Integer
---------- ---------- ----------
ddd 1.5 1
eee 2.3 0
fff 1.2 NULL
ggg 6.123 1
如您所见，项目现在按字符串中的原始顺序排序。
感谢您的挑战，我已经有一段时间没有像样的挑战了:-)
第一次尝试
好吧，首先您必须将该字符串拆分到一个表中。为此，您应该使用用户定义的函数。您可以从 Aaron Bertrand 的产品中选择最适合您的一款以正确的方式分割字符串——或者次佳的方式 http://sqlperformance.com/2012/07/t-sql-queries/split-strings文章。
对于本次演示，我选择使用SplitStrings_XML.
首先，创建函数：
CREATE FUNCTION dbo.SplitStrings_XML
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
FROM
(
SELECT x = CONVERT(XML, '<i>'
+ REPLACE(@List, @Delimiter, '</i><i>')
+ '</i>').query('.')
) AS a CROSS APPLY x.nodes('i') AS y(i)
);
GO
现在，声明并初始化变量：
declare @string nvarchar(max) = 'ddd,1.5,1,eee,2.3,0,fff,1.2,ggg,6.123,1'
然后，创建 4公用表表达式 https://msdn.microsoft.com/en-us/library/ms175972.aspx- 一项用于所有项目，一项用于字符串，一项用于双精度数，一项用于整数。注意使用row_number() https://msdn.microsoft.com/en-us/library/ms186734.aspx函数 - 稍后将使用它将所有结果连接在一起：
;WITH AllItems as
(
SELECT Item, ROW_NUMBER() OVER(ORDER BY (select null)) as rn
FROM dbo.SplitStrings_XML(@string, ',')
)
, Strings as
(
SELECT Item as StringItem, ROW_NUMBER() OVER(ORDER BY (select null)) as rn
FROM dbo.SplitStrings_XML(@string, ',')
WHERE ISNUMERIC(Item) = 0
), Doubles as
(
SELECT Item as DoubleItem, ROW_NUMBER() OVER(ORDER BY (select null)) as rn
FROM dbo.SplitStrings_XML(@string, ',')
WHERE ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) > 0
), Integers as
(
SELECT Item as IntegerItem, ROW_NUMBER() OVER(ORDER BY (select null)) as rn
FROM dbo.SplitStrings_XML(@string, ',')
WHERE ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) = 0
)
然后，从连接所有这些公共表表达式中进行选择。注意使用COALESCE https://msdn.microsoft.com/en-us/library/ms190349.aspx内置函数仅返回至少存在一个值的行：
SELECT StringItem, DoubleItem, IntegerItem
FROM AllItems A
LEFT JOIN Strings S ON A.rn = S.rn
LEFT JOIN Doubles D ON A.rn = D.rn
LEFT JOIN Integers I ON A.rn = I.rn
WHERE COALESCE(StringItem, DoubleItem, IntegerItem) IS NOT NULL
Results:
StringItem DoubleItem IntegerItem
---------- ---------- -----------
ddd 1.5 1
eee 2.3 0
fff 1.2 1
ggg 6.123 NULL
