create table test(车队 int,日期 smalldatetime,公里 int)
insert into test select 1,'2006-7-7',50
insert into test select 2,'2006-7-7',67
insert into test select 1,'2006-7-8',89
insert into test select 2,'2006-7-8',45
declare @sql varchar(8000)
set @sql=''
select
@sql=@sql+',['+t.日期+']=sum(case 日期 when '''+t.日期+''' then 公里 else 0 end)'
from
(select distinct convert(char(10),日期,120) as 日期 from test) t
order by
t.日期
set @sql='select 车队'+@sql+' from test group by 车队 order by 车队'
---- 给你一个动态的例子 ----
CREATE TABLE tb(car varchar(2),rq datetime,gl int)
INSERT tb SELECT '1', '2006-7-7', 50
UNION ALL SELECT '2', '2006-7-7', 67
UNION ALL SELECT '1', '2006-7-8', 89
UNION ALL SELECT '2', '2006-7-8', 45
select * from tb
DECLARE @s nvarchar(4000)
SET @s='SELECT car'
SELECT @s=@s
+','+QUOTENAME(rq)
+N'=SUM(CASE rq WHEN '+QUOTENAME(rq,N'''')
+N' THEN gl END)'
FROM tb
GROUP BY rq
print (@s+N'
FROM tb
GROUP BY car')
create table test(车队 int,日期 varchar(10),公里 int)
insert into test select 1,'2006-7-7',50
insert into test select 2,'2006-7-7',67
insert into test select 1,'2006-7-8',89
insert into test select 2,'2006-7-8',45
select 车队, max(case when 日期='2006-7-7' then 公里 end) as '2006-7-7',
max(case when 日期='2006-7-8' then 公里 end )as '2006-7-8'
from test
group by 车队
select
车队,
[2006-7-7]=sum(case 日期 when '2006-7-7' then 公里 else 0 end),
[2006-7-8]=sum(case 日期 when '2006-7-8' then 公里 else 0 end)
from
test
group by
车队
order by
车队
create table test(车队 int,日期 varchar(10),公里 int)
insert into test select 1,'2006-7-7',50
insert into test select 2,'2006-7-7',67
insert into test select 1,'2006-7-8',89
insert into test select 2,'2006-7-8',45
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',['+日期+']=sum(case 日期 when '''+日期+''' then 公里 else 0 end)' from test group by 日期
set @sql='select 车队'+@sql+' from test group by 车队 order by 车队'