除了dynamic之外
PIVOT，您还可以使用 Dynamic
Crosstab
来做到这一点，出于可读性的考虑，我更喜欢这样做。
SQL小提琴
DECLARE @sql1 VARCHAr(1000) = '', @sql2 VARCHAr(1000) = '', @sql3 VARCHAr(1000) = ''DECLARE @max INTSELECt TOP 1 @max = COUNT(*) FROM PersonTest GROUP BY ID ORDER BY COUNT(*) DESCSELECt @sql1 = 'SELECT ID' + CHAr(10)SELECT @sql2 = @sql2 +' , MAX(CASE WHEN RN =' + ConVERT(VARCHAr(5), RN) + ' THEN name END) AS ' + QUOTENAME('Name' + ConVERT(VARCHAr(5), RN)) + CHAr(10)FROM( SELECt TOP(@max) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RN FROM sys.columns)tORDER BY RNSELECt @sql3 ='FROM( SELECt *, RN = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY name) FROM PersonTest)tGROUP BY IDORDER BY ID'PRINT (@sql1 + @sql2 + @sql3)EXEC (@sql1 + @sql2 + @sql3)
