如果您现在知道结果中将要包含多少列，则需要使用动态T-SQL语句来构建
PIVOT。例如：
IF OBJECT_ID('tempdb..#DataSource') IS NOT NULLBEGIN; DROp TABLE #DataSource;END;CREATE TABLE #DataSource( [id] INT ,[Code] INT ,[EntityId] INT);DECLARE @DynamicTSQLStatement NVARCHAr(MAX) ,@Columns NVARCHAr(MAX);DECLARE @MaxColumns INT;INSERT INTO #DataSource ([id], [Code], [EntityId])VALUES (3, 22209776 , 1) ,(4, 143687971, 3) ,(4, 143687971, 4) ,(4, 143687971, 5) ,(4, 143687971, 15) ,(5, 143658155, 7) ,(5, 143658155, 8) ,(4, 143687971, 25) ,(4, 143687971, 26);-- we need to know how many columns are going to be shownSELECT TOP 1 @MaxColumns = COUNT(*)FROM #DataSourceGROUP BY [Code]ORDER BY COUNT(*) DESC;-- we are building here the following string '[1],[2],[3],[4],[5],[6]'; -- this will change depending the input dataWITH gen AS ( SELECt 1 AS num UNIOn ALL SELECT num+1 FROM gen WHERe num+1<=@MaxColumns)SELECt @Columns = STUFF( ( SELECT ',[EntityId' + CAST([num] AS VARCHAr(12)) + ']' FROM gen FOR XML PATH(''), TYPE ).value('.', 'VARCHAr(MAX)') ,1 ,1 ,'')OPTION (maxrecursion 10000);SET @DynamicTSQLStatement = N'SELECt *FROM ( SELECt [id] ,[Code] ,[EntityId] ,''EntityId'' + CAST(ROW_NUMBER() OVER(PARTITION BY [Code] ORDER BY [EntityId]) AS VARCHAr(12)) FROM #DataSource) DS ([id], [Code], [EntityId], [RowID])PIVOT ( MAX([EntityId]) for [RowID] in (' + @Columns +')) PVT;';EXEC sp_executesql @DynamicTSQLStatement;
