create table [Data_NewTrayGuide_Picture] ( [vpictureid] int IDENTITY(1,1) NOT NULL, [VID] int not null, [title] varchar(2000) not null ) go /**********插入测试数据*******************/ declare @a int,@vid int set @a=10 while @a<=200 begin set @vid=@a/4 insert into [Data_NewTrayGuide_Picture] values(@vid,'[thetitle]'+cast(@a as varchar(200))) set @a=@a+1 end go /***********正题开始******************/ select * from [Data_NewTrayGuide_Picture] /**************************************************/ SELECT * INTO #tt FROM Data_NewTrayGuide_Picture --吧数据添加到临时表里面去 go /*********************************************************/ with Data_NewTrayGuide_Picture as ( select ORDERID=(select count(vid) FROM #tt T1 WHERE T1.vid=T2.vid AND T1.vpictureid<=T2.vpictureid), * FROM #tt T2 ) select vid ,MAX(CASE ORDERID WHEN 1 THEN title ELSE '' END) as vpicID1, MAX(CASE ORDERID WHEN 2 THEN title ELSE '' END) as vpicID2, MAX(CASE ORDERID WHEN 3 THEN title ELSE '' END) as vpicID3 from Data_NewTrayGuide_Picture group by vid order by vid /***************************************************************/
