Based on 3. solution, the correct sql code is:
DECLARE @tmp TABLE (ID INT IDENTITY(1,1), val VARCHAR(30))
INSERT INTO @tmp (val)
SELECT '1' AS val
UNION ALL SELECT '22' AS val
UNION ALL SELECT '333' AS val
UNION ALL SELECT '4444' AS val
UNION ALL SELECT '55555' AS val
SELECT val, CASE WHEN CastingOutNines=0 THEN 9 ELSE CastingOutNines END AS SumOfDigits
FROM (
SELECT val, ABS(CONVERT(INT, val)) % 9 AS CastingOutNines
FROM @tmp
) AS T
Result:
val SumOfDigits
1 1
22 4
333 9
4444 7
55555 7
Why? To avoid
0
(zeros) in case of sum of digits is equal
9
;)
3+3+3=9
Another way is to use
Common Table Expressions[
^]:
DECLARE @myFirstSums TABLE(ID INT, val NVARCHAR(30), SumOfDigits INT)
;WITH myValues AS
(
SELECT ID, val, CONVERT(INT, LEFT(val,1)) AS newVal, RIGHT(val, LEN(val)-1) AS Remainder
FROM @tmp
WHERE LEN(val)>=1
UNION ALL
SELECT ID, val, CONVERT(INT, LEFT(Remainder,1)) AS newVal, RIGHT(Remainder, LEN(Remainder)-1) AS Remainder
FROM myValues
WHERE LEN(Remainder)>=1
UNION ALL
SELECT ID, val, CONVERT(INT, Remainder) AS newVal, NULL AS Remainder
FROM myValues
WHERE LEN(Remainder)=0
)
INSERT INTO @myFirstSums (ID, val, SumOfDigits)
SELECT ID, val, SUM(newVal) AS SumOfDigits
FROM myValues
GROUP BY ID, val
ORDER BY ID, val
;WITH mySums AS
(
SELECT ID, val, SumOfDigits, CONVERT(INT, LEFT(CONVERT(VARCHAR(10),SumOfDigits),1)) AS newVal, RIGHT(CONVERT(VARCHAR(10),SumOfDigits), LEN(CONVERT(VARCHAR(10),SumOfDigits))-1) AS Remainder
FROM @myFirstSums
WHERE LEN(CONVERT(VARCHAR(10),SumOfDigits))>=1
UNION ALL
SELECT ID, val, SumOfDigits, CONVERT(INT, LEFT(Remainder,1)) AS newVal, RIGHT(Remainder, LEN(Remainder)-1) AS Remainder
FROM mySums
WHERE LEN(Remainder)>=1
UNION ALL
SELECT ID, val, SumOfDigits, CONVERT(INT, Remainder) AS newVal, NULL AS Remainder
FROM mySums
WHERE LEN(Remainder)=0
)
SELECT ID, val, SUM(newVal) AS SumOfDigits
FROM mySums
GROUP BY ID, val
The result is the same ;)
Warning! Above example (using CTE) supports only the numbers when the sum of digits is less than 100 ;)