Java多行合并一列_SQL数据多行合并成一行多列

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

/***************************************************************/