如何在SQL Server中将分组多行数据转为最多10列的行结构？

我有一个包含多行数据的表，需要按CloGroup字段分组，将每组内的ColNumber依次填入每行的Col1至Col10列，当每组数据超过10条时自动换行到下一行。

示例数据创建SQL #

SELECT T.*
INTO #Temps
FROM
(SELECT 
'A' AS CloGroup,    1 AS ColNumber UNION ALL SELECT
'A',    2 UNION ALL SELECT
'A',    3 UNION ALL SELECT
'A',    4 UNION ALL SELECT
'A',    5 UNION ALL SELECT
'A',    6 UNION ALL SELECT
'A',    7 UNION ALL SELECT
'A',    8 UNION ALL SELECT
'A',    9 UNION ALL SELECT
'A',    10 UNION ALL SELECT
'A',    11 UNION ALL SELECT
'A',    12 UNION ALL SELECT
'A',    13 UNION ALL SELECT
'A',    14 UNION ALL SELECT
'A',    15 UNION ALL SELECT
'A',    16 UNION ALL SELECT
'A',    17 UNION ALL SELECT
'A',    18 UNION ALL SELECT
'A',    19 UNION ALL SELECT
'A',    20 UNION ALL SELECT
'A',    21 UNION ALL SELECT
'A',    22 UNION ALL SELECT
'B',    30 UNION ALL SELECT
'B',    31 UNION ALL SELECT
'B',    32 UNION ALL SELECT
'B',    33 UNION ALL SELECT
'B',    34 UNION ALL SELECT
'B',    35 UNION ALL SELECT
'B',    30 UNION ALL SELECT
'B',    31 UNION ALL SELECT
'B',    32 UNION ALL SELECT
'B',    33 UNION ALL SELECT
'B',    40 UNION ALL SELECT
'B',    41 UNION ALL SELECT
'B',    42 UNION ALL SELECT
'B',    43 UNION ALL SELECT
'B',    40) T

SELECT #Temps.* FROM #Temps

DROP TABLE #Temps

期望输出格式 #

Group-Col1--Col2--Col3--Col4--Col5--Col6--Col7--Col8--Col9--Col10
A-----1-----2-----3-----4-----5-----6-----7-----8-----9-----10
A-----11----12----13----14----15----16----17----18----19----20
B-----30----31----32----33----34----35----30----31----32----33
B-----40----41----42----43----40

实现方案 #

通过行号分组+透视转换的方式可以实现需求，具体步骤如下：

1. 给每组内的每条数据分配连续行号，确保数据顺序；
2. 根据行号计算每条数据对应的目标行（每10条为一行）；
3. 计算数据对应的列位置（映射到Col1至Col10）；
4. 使用PIVOT函数将行数据转换为列结构。

具体SQL代码 #

WITH RankedData AS (
    SELECT 
        CloGroup,
        ColNumber,
        -- 每组内生成连续行号
        ROW_NUMBER() OVER (PARTITION BY CloGroup ORDER BY (SELECT NULL)) AS RowNum,
        -- 计算当前数据属于第几行（每10条换一行）
        ((ROW_NUMBER() OVER (PARTITION BY CloGroup ORDER BY (SELECT NULL)) - 1) / 10) + 1 AS GroupRow
    FROM #Temps
),
PivotData AS (
    SELECT 
        CloGroup,
        GroupRow,
        -- 生成目标列名Col1-Col10
        'Col' + CAST(((RowNum - 1) % 10) + 1 AS VARCHAR) AS ColName,
        ColNumber
    FROM RankedData
)
SELECT 
    CloGroup AS [Group],
    Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10
FROM PivotData
PIVOT (
    MAX(ColNumber)
    FOR ColName IN (Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10)
) AS PivotResult
ORDER BY CloGroup, GroupRow;

代码说明 #

• ROW_NUMBER()：在每个CloGroup分组内生成连续行号，保证数据按原始顺序排列；
• ((RowNum -1)/10)+1：通过整数除法将每10条数据归为同一目标行，实现自动换行；
• ((RowNum -1)%10)+1：通过取余运算确定数据在目标行中的列位置；
• PIVOT：将行格式的数据转换为列格式，最终按分组和目标行号排序输出。

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