MS SQL Server同UserCode不同ID数据的无UNION并排展示方案

我有一组数据，需求是将同一usercode下、不同ID的数据按ID从小到大横向并排展示，且不能使用UNION语句。例如原本每行存储一个用户的单条ID与对应数据，需要转换为一行内依次排列该用户的第1条、第2条……第N条数据。

我之前的尝试 #

先为每个usercode下的数据添加序号并存入临时表，再通过JOIN结合UNION查询，代码如下：

DROP TABLE #TEMP
CREATE TABLE #TEMP (
    ID INT,
    [data] INT,
    usercode NVARCHAR(50),
    RowNum INT
)

INSERT INTO #TEMP(ID, [data], UserCode, RowNum)
SELECT Id, ApplicationID, 'john', ROW_NUMBER() OVER (ORDER BY Usercode) RNum from UserApplicationAccess

后续查询代码：

select a.UserCode, a.RowNum Id_1, a.[data] data_1, b.RowNum Id_2, b.[data] data_2 from #TEMP a join #TEMP b on a.UserCode = b.UserCode 
where a.RowNum = 1 and b.RowNum = 2 
union
select a.UserCode, a.RowNum Id_1, a.[data] data_1, b.RowNum Id_2, b.[data] data_2 from #TEMP a join #TEMP b on a.UserCode = b.UserCode 
where a.RowNum = 2 and b.RowNum = 3

但该方案存在明显局限性：每个usercode对应的数据行数不固定（部分用户可能有10条甚至更多数据），需要编写多次UNION，完全无法通用，寻求更优解决方案。

解决方案：动态SQL结合多表JOIN实现 #

由于数据行数不固定，静态SQL无法适配所有场景，使用动态SQL可自动根据用户的最大数据行数生成对应列与关联逻辑，无需手动编写重复UNION。

实现代码

-- 1. 生成带用户分组行号的临时表（修正原方案行号无分组的问题）
DROP TABLE IF EXISTS #UserData
CREATE TABLE #UserData (
    usercode NVARCHAR(50),
    ID INT,
    [data] INT,
    RowNum INT
)

INSERT INTO #UserData(usercode, ID, [data], RowNum)
SELECT 
    usercode,
    ID,
    ApplicationID AS [data],
    ROW_NUMBER() OVER (PARTITION BY usercode ORDER BY ID) AS RowNum
FROM UserApplicationAccess

-- 2. 获取所有用户中的最大数据行数
DECLARE @MaxRowNum INT
SELECT @MaxRowNum = MAX(RowNum) FROM #UserData

-- 3. 动态生成SELECT列与JOIN语句
DECLARE @SelectClause NVARCHAR(MAX) = 'SELECT u1.usercode'
DECLARE @JoinClause NVARCHAR(MAX) = 'FROM #UserData u1 WHERE u1.RowNum = 1'
DECLARE @i INT = 2

WHILE @i <= @MaxRowNum
BEGIN
    -- 拼接当前行号对应的ID和data列
    SET @SelectClause += ', u' + CAST(@i AS NVARCHAR) + '.ID AS Id_' + CAST(@i AS NVARCHAR)
    SET @SelectClause += ', u' + CAST(@i AS NVARCHAR) + '.data AS data_' + CAST(@i AS NVARCHAR)
    
    -- 拼接LEFT JOIN关联当前行号的数据
    SET @JoinClause += ' LEFT JOIN #UserData u' + CAST(@i AS NVARCHAR) 
                    + ' ON u1.usercode = u' + CAST(@i AS NVARCHAR) + '.usercode'
                    + ' AND u' + CAST(@i AS NVARCHAR) + '.RowNum = ' + CAST(@i AS NVARCHAR)
    
    SET @i += 1
END

-- 4. 组合并执行最终SQL
DECLARE @FinalSQL NVARCHAR(MAX) = @SelectClause + ' ' + @JoinClause
EXEC sp_executesql @FinalSQL

关键说明

• 修正原方案的行号生成逻辑：使用PARTITION BY usercode确保每个用户的行号独立从1开始计数，避免跨用户行号混乱
• 动态生成列与关联逻辑：根据所有用户的最大数据行数，自动生成对应数量的Id_N、data_N列，以及关联每个行号数据的LEFT JOIN语句
• 兼容行数差异：使用LEFT JOIN保证即使部分用户数据行数少于最大值，也能正常展示（不足的列显示NULL），不会丢失用户数据

内容的提问来源于stack exchange，提问作者Brown_MV