你可以用 row_number() 要生成行号，然后进行聚合：

select max(case when a = 'A' then b end) as a,
       max(case when a = 'B' then b end) as b,
       max(case when a = 'C' then b end) as c
from (select t.*, row_number() over (partition by a order by b) as seqnum
      from t
     ) t
group by seqnum
order by seqnum;

正如戈登林诺夫所说 row_number() 为了生成行号，然后进行聚合，基于这个和这个答案，我添加了一个temp列作为 row_number() 作为轴心。

DECLARE @PivotColumnNames AS NVARCHAR(MAX),
        @PivotSelectColumnNames AS NVARCHAR(MAX),
        @DynamicPivotQuery AS NVARCHAR(MAX); 

--Get distinct values of the PIVOT Column
SELECT @PivotColumnNames= ISNULL(@PivotColumnNames + ',','')
+ QUOTENAME(COLUMN_A)
FROM (SELECT DISTINCT COLUMN_A FROM t) AS A

--Get distinct values of the PIVOT Column with isnull
SELECT @PivotSelectColumnNames 
= ISNULL(@PivotSelectColumnNames + ',','')
+ 'ISNULL(' + QUOTENAME(COLUMNA) + ', '') AS '
+ QUOTENAME(COLUMN_A)
FROM (SELECT DISTINCT COLUMN_A FROM t) AS A

WITH PivotData
AS (
    SELECT COLUMN_A --The row group
        , ROW_NUMBER() OVER (
                PARTITION BY COLUMN_A ORDER BY COLUMN_A ASC
                ) AS TheRow --The value that will form columns
        ,COLUMN_B
    FROM 
        t
    )
SELECT '+@PivotSelectColumnNames+'
FROM PivotData
PIVOT(max(COLUMN_B) FOR COLUMN_A IN (
            '+@PivotColumnNames+'
            )) AS P