有关PIVOT方法的详细信息。
试试下面的代码：
-- Temporary table...create table ##myTable ( IDs int ,[Date] datetime ,[Event] varchar(1) )-- ... with sample datainsert ##myTable select 102, '2010-01-01', 'A'union select 102, '2010-01-01', 'B'union select 102, '2010-01-01', 'C'union select 102, '2010-01-01', 'E'union select 103, '2010-01-01', 'A'union select 104, '2010-01-01', 'B'union select 104, '2010-01-01', 'C'union select 105, '2010-01-01', 'F'-- VariablesDECLARE @cols AS NVARCHAr(MAX) ,@query AS NVARCHAr(MAX)-- Build column name for our result.-- The ROW_NUMBER() operator gives us the rank of the event for-- the combination of IDs and Date. With that, event B for IDs 104-- will have rank 1, and then will appear in the 1st column.SELECT @cols = STUFF( (SELECT DISTINCT ',' + QUOTENAME('Event' + LTRIm(STR( ROW_NUMBER() OVER ( PARTITION BY IDs, [Date] ORDER BY IDs, [Date] ) ))) FROM ##myTable FOR XML PATH(''), TYPE).value('.', 'NVARCHAr(MAX)') , 1, 1, '')set @query = ' SELECt IDs, [Date], ' + @cols + ' FROM ( SELECt IDs ,[Date] ,[Event] ,''Event'' + LTRIm(STR( ROW_NUMBER() OVER ( PARTITION BY IDs, [Date] ORDER BY IDs, [Date] ) )) as [EventNo] FROM ##myTable ) x PIVOT ( MAX([Event]) FOR [EventNo] IN (' + @cols + ') ) p'execute sp_executesql @query-- Remove temporary tabledrop table ##myTable
