SQL Server-列的和逗号分隔值

使用SQL Server 2005+CTE,您可以创建递归选择,例如

DECLARE @Table TABLE(
        ID INT,
        Vals VARCHAR(100)
)

INSERT INTO @Table SELECT 1, '0.00,12.45,14.33'
INSERT INTO @Table SELECT 2, '1,2,3,4'

;WITH ValList AS(
        SELECT  ID,
                CAST(LEFT(Vals,PATINDEX('%,%', Vals) - 1) AS FLOAT) Val,
                RIGHT(Vals,LEN(Vals) - PATINDEX('%,%', Vals)) Remainder
        FROM    @Table
        UNION ALL
        SELECT  ID,
                CAST(LEFT(Remainder,CASE WHEN PATINDEX('%,%', Remainder) = 0 THEN LEN(Remainder) ELSE PATINDEX('%,%', Remainder) - 1 END) AS FLOAT) Val,
                RIGHT(Remainder,CASE WHEN PATINDEX('%,%', Remainder) = 0 THEN 0 ELSE LEN(Remainder) - PATINDEX('%,%', Remainder) END) Remainder
        FROM    ValList
        WHERE   LEN(Remainder) > 0

)
SELECT  ID,
        SUM(Val)
FROM    ValList
GROUP BY ID

产量

ID          Total
----------- ----------------------
1           26.78
2           10

在一个函数中,你可以尝试这样的方法,完全不确定它是否会起作用。

CREATE FUNCTION ufn_sum_csv(@string varchar(100))
RETURNS @result int
AS BEGIN
EXEC 'SELECT @result = ' + REPLACE(@string,',','+')
RETURN

不能在这个比赛中试一试。