if object_id('[S_Aut]') is not null drop table [S_Aut] go create table [S_Aut]([id] int,[usename] varchar(4),[autoname] varchar(6),[outtime] datetime,[location_1] varchar(4),[location_2] varchar(5),[location_3] varchar(3),[endtime] datetime,[kilometer_no] int) insert [S_Aut] select 25,'李明','捷安特','2012/5/14 15:17:00','测试','wqe','ada','1900/1/1 0:00:00',0 union all select 26,'王刚','永久','2012/5/14 16:17:00','测试','gfdfg',null,'1900/1/1 0:00:00',0 union all select 27,'张飞','捷安特','2012/5/14 15:17:00','测试','retre','rte','1900/1/1 0:00:00',0 union all select 28,'王刚','永久','2012/5/14 15:17:00','测试','edfs','tre','1900/1/1 0:00:00',0 union all select 29,'陈明','法拉利','2012/5/14 16:17:00','测试','ngnh',null,'1900/1/1 0:00:00',0 union all select 30,'唐逸','凤凰','2012/5/14 19:17:00','测试','ghj','rt','1900/1/1 0:00:00',0 godeclare @sql varchar(max) select @sql=isnull(@sql+',','') +'max(case when rn='+ltrim(rn)+' then usename end) as [usename],' +'max(case when rn='+ltrim(rn)+' then location_1 end) as [location_1],' +'max(case when rn='+ltrim(rn)+' then location_2 end) as [location_2],' +'max(case when rn='+ltrim(rn)+' then location_3 end) as [location_3]' from (select distinct rn=row_number() over(partition by autoname,outtime order by getdate()) from s_aut) texec ('select autoname,' +@sql +',outtime from (select *,rn=row_number() over(partition by autoname,outtime order by getdate()) from s_aut) t group by autoname,outtime' )/** autoname usename location_1 location_2 location_3 usename location_1 location_2 location_3 outtime -------- ------- ---------- ---------- ---------- ------- ---------- ---------- ---------- ----------------------- 捷安特 张飞 测试 retre rte 李明 测试 wqe ada 2012-05-14 15:17:00.000 永久 王刚 测试 edfs tre NULL NULL NULL NULL 2012-05-14 15:17:00.000 法拉利 陈明 测试 ngnh NULL NULL NULL NULL NULL 2012-05-14 16:17:00.000 永久 王刚 测试 gfdfg NULL NULL NULL NULL NULL 2012-05-14 16:17:00.000 凤凰 唐逸 测试 ghj rt NULL NULL NULL NULL 2012-05-14 19:17:00.000(5 行受影响) **/
go
create table [S_Aut]([id] int,[usename] varchar(4),[autoname] varchar(6),[outtime] datetime,[location_1] varchar(4),[location_2] varchar(5),[location_3] varchar(3),[endtime] datetime,[kilometer_no] int)
insert [S_Aut]
select 25,'李明','捷安特','2012/5/14 15:17:00','测试','wqe','ada','1900/1/1 0:00:00',0 union all
select 26,'王刚','永久','2012/5/14 16:17:00','测试','gfdfg',null,'1900/1/1 0:00:00',0 union all
select 27,'张飞','捷安特','2012/5/14 15:17:00','测试','retre','rte','1900/1/1 0:00:00',0 union all
select 28,'王刚','永久','2012/5/14 15:17:00','测试','edfs','tre','1900/1/1 0:00:00',0 union all
select 29,'陈明','法拉利','2012/5/14 16:17:00','测试','ngnh',null,'1900/1/1 0:00:00',0 union all
select 30,'唐逸','凤凰','2012/5/14 19:17:00','测试','ghj','rt','1900/1/1 0:00:00',0
godeclare @sql varchar(max)
select @sql=isnull(@sql+',','')
+'max(case when rn='+ltrim(rn)+' then usename end) as [usename],'
+'max(case when rn='+ltrim(rn)+' then location_1 end) as [location_1],'
+'max(case when rn='+ltrim(rn)+' then location_2 end) as [location_2],'
+'max(case when rn='+ltrim(rn)+' then location_3 end) as [location_3]'
from
(select distinct rn=row_number() over(partition by autoname,outtime order by getdate()) from s_aut) texec ('select autoname,'
+@sql
+',outtime from (select *,rn=row_number() over(partition by autoname,outtime order by getdate()) from s_aut) t group by autoname,outtime'
)/**
autoname usename location_1 location_2 location_3 usename location_1 location_2 location_3 outtime
-------- ------- ---------- ---------- ---------- ------- ---------- ---------- ---------- -----------------------
捷安特 张飞 测试 retre rte 李明 测试 wqe ada 2012-05-14 15:17:00.000
永久 王刚 测试 edfs tre NULL NULL NULL NULL 2012-05-14 15:17:00.000
法拉利 陈明 测试 ngnh NULL NULL NULL NULL NULL 2012-05-14 16:17:00.000
永久 王刚 测试 gfdfg NULL NULL NULL NULL NULL 2012-05-14 16:17:00.000
凤凰 唐逸 测试 ghj rt NULL NULL NULL NULL 2012-05-14 19:17:00.000(5 行受影响)
**/
(百年树人)
呵呵 你帮过我很多次了 每次都是很难的题 万分感谢 等下在开个贴 以示感激