满足此类要求的***解决方案是使用公用表格表达式 [ ^ ]（CTE）将逗号上的数据拆分成行然后使用 CROSS JOIN [ ^ ]加入CTE返回的所有结果集。

试试这个：

The best solution for such of requirements is to use Common Table Expressions[^] (CTE) to split data on comma into rows and then to use CROSS JOIN[^] to join all resultsets returned by CTE's.
Try this:

Declare @User NVARCHAR(500) = '108,124', 
    @Role INT = 5,  
    @cate INT = 1,        
    @Type INT = 3,
    @Item NVARCHAR(500) = '3,4,5',
    @BU NVARCHAR(500) = '57,58,31'


;WITH Users AS
(
	SELECT CONVERT(INT, LEFT(@user, CHARINDEX(',', @User)-1)) AS U, RIGHT(@user, LEN(@User) - CHARINDEX(',', @User)) AS Remainder
	WHERE CHARINDEX(',', @User)>0
	UNION ALL
	SELECT CONVERT(INT, LEFT(Remainder, CHARINDEX(',', Remainder)-1)) AS U, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
	FROM Users
	WHERE CHARINDEX(',', Remainder)>0
	UNION ALL
	SELECT CONVERT(INT, Remainder) AS U, NULL AS Remainder
	FROM Users
	WHERE CHARINDEX(',', Remainder)=0
), Items AS
(
	SELECT CONVERT(INT, LEFT(@Item, CHARINDEX(',', @Item)-1)) AS I, RIGHT(@Item, LEN(@Item) - CHARINDEX(',', @Item)) AS Remainder
	WHERE CHARINDEX(',', @Item)>0
	UNION ALL
	SELECT CONVERT(INT, LEFT(Remainder, CHARINDEX(',', Remainder)-1)) AS I, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
	FROM Items
	WHERE CHARINDEX(',', Remainder)>0
	UNION ALL
	SELECT CONVERT(INT, Remainder) AS I, NULL AS Remainder
	FROM Items
	WHERE CHARINDEX(',', Remainder)=0
), BUs AS
(
	SELECT CONVERT(INT, LEFT(@BU, CHARINDEX(',', @BU)-1)) AS B, RIGHT(@BU, LEN(@BU) - CHARINDEX(',', @BU)) AS Remainder
	WHERE CHARINDEX(',', @BU)>0
	UNION ALL
	SELECT CONVERT(INT, LEFT(Remainder, CHARINDEX(',', Remainder)-1)) AS B, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
	FROM BUs
	WHERE CHARINDEX(',', Remainder)>0
	UNION ALL
	SELECT CONVERT(INT, Remainder) AS B, NULL AS Remainder
	FROM BUs
	WHERE CHARINDEX(',', Remainder)=0
)
--INSERT INTO YourTableNameHere ([User], Cate, [Type], Item, BU, Role)
SELECT u.U AS [User], @cate As Cate, @Type AS [Type], i.I AS Item, b.B AS BU, @Role AS Role
FROM Users AS u, Items As i, BUs As b
ORDER BY [User]

按预期返回结果。有关详细信息，请参阅：

WITH common_table_expression（Transact-SQL） [ ^ ]

使用公用表格式 [ ^ ]

使用公用表格表达式的递归查询 [ ^ ]

SQL连接的可视化表示 [ ^ ]

Returns results as expected. For further details, please see:
WITH common_table_expression (Transact-SQL)[^]
Using Common Table Expressions[^]
Recursive Queries Using Common Table Expressions[^]
Visual Representation of SQL Joins[^]

我创建了一个可重用的函数，用于将日期拆分为表，函数名称为split。

Hi,

I have created one reusable function for splitting the date into a table and the function name is "split".

CREATE FUNCTION split (
@delimitedString VARCHAR(1000),
@delimitedFormat VARCHAR(10) 
)
RETURNS  @a TABLE(Id smallint IDENTITY(1,1),
value VARCHAR(100)
)

AS
BEGIN 

DECLARE @charIndex smallInt =0 
DECLARE @charlength smallInt

WHILE LEN(@delimitedString) > 0
BEGIN 
	SET @charIndex = CHARINDEX(@delimitedFormat,@delimitedString)
	SET @charlength = 0 
	IF @charIndex = 0 
		Begin
			INSERT INTO @a(value)
			SELECT  @delimitedString
			
			RETURN 
		END
	ELSE 
		BEGIN 
			INSERT INTO @a(value)
			SELECT  SUBSTRING(@delimitedString,1,@charIndex-1)
			
			SET @charlength = @charlength + @charIndex 
			SET @delimitedString = SUBSTRING(@delimitedString,@charlength+1,LEN(@delimitedString)-@charIndex + 1)
			
		END	
	END		
RETURN
END

我用过@ User，@ Item，@ BU变量的相同功能，它产生一个表。并使用CROSS join加入这些表。如果像@Item这样的变量具有Null数据，则以下查询可能不会产生任何输出。在这种情况下，我们可以使用OUTER APPLY代替CROSS join。但是，CROSS join比OUTER APPLY具有更高的性能。

I have used same function for "@User", "@Item","@BU" variables and it results a table. And joined these tables using CROSS join. If a variable like @Item is having Null data then the following query might not result any output. In this case we can go with the "OUTER APPLY" instead "CROSS join". However, "CROSS join" has more performance than the "OUTER APPLY".

Declare @User NVARCHAR(500) = '108,124', 
    @Role INT = 5,  
    @cate INT = 1,        
    @Type INT = 3,
    @Item NVARCHAR(500) = '3,4,5',
    @BU NVARCHAR(500) = '57,58,31'

SELECT u.value AS 'user',cate,Type,i.value AS 'Item',bu.value AS 'BU',Role 
from split(@User,',')  u
CROSS join (SELEcT   @cate AS cate ) cate
CROSS join (SELEcT   @Type AS Type ) Type
CROSS join split(@Item,',') i
CROSS join split(@BU,',') bu
CROSS join (SELEcT   @Role AS Role ) Role
ORDER BY u.value