goofyui goofyui - 5 months ago 21
SQL Question

SQL Comma Split function for nth loop iteration

i have a dynamic integer variable where the count number is loaded dynamically.

iCount = 3
or iCount = 10 ( dynamically number is loaded ).

I have to split the number as 1,2,3 for the iCount = 3
1,2,3,4,5,6,7,8,9,10 for the iCount = 10
and 1 for the iCount = 1.


How can we achive the split functionality through nth variable in SQL ?

Answer
DECLARE @iCount int = 10, @iCountRef varchar(100)

;WITH cte AS (
SELECT 1 as i
UNION ALL
SELECT i+1
FROM cte
WHERE i < @iCount
)

SELECT @iCountRef = STUFF((
SELECT ',' + CAST(i as nvarchar(10))
FROM cte
FOR XML PATH('')),1,1,'')

SELECT @iCountRef

Output for 3:

1,2,3

Output for 1:

1

Output for 10:

1,2,3,4,5,6,7,8,9,10