sql - 用于第n个循环迭代的SQL逗号分割功能

我有一个动态整数变量，其中计数是动态加载的。

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.

如何在SQL中通过nth变量实现split功能？

最佳答案

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

3的输出：

1,2,3

1的输出：

1

10的输出：

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