create table cb ( itime datetime, ityp varchar(10), ivalue int ) insert into cb (itime,ityp,ivalue)values('2007-08-31 16:00:00.000','A',72) insert into cb (itime,ityp,ivalue)values('2007-09-11 16:00:00.000','B',64) insert into cb (itime,ityp,ivalue)values('2007-05-11 16:00:00.000','C',71) insert into cb (itime,ityp,ivalue)values('2007-09-11 16:00:00.000','D',78) insert into cb (itime,ityp,ivalue)values('2007-09-11 16:00:00.000','E',76)select itime, A=max(case ityp when 'A' then ivalue else 0 end), B=max(case ityp when 'B' then ivalue else 0 end), C=max(case ityp when 'C' then ivalue else 0 end), D=max(case ityp when 'D' then ivalue else 0 end), E=max(case ityp when 'E' then ivalue else 0 end) from cb group by itime order by itime
或者 select itime, case ityp when 'A' then ivalue else 0 end as A, case ityp when 'B' then ivalue else 0 end as B, case ityp when 'C' then ivalue else 0 end as C, case ityp when 'D' then ivalue else 0 end as D, case ityp when 'E' then ivalue else 0 end as E from cb group by itime ,ityp,ivalue order by itime
(
itime datetime,
ityp varchar(10),
ivalue int
)
insert into cb (itime,ityp,ivalue)values('2007-08-31 16:00:00.000','A',72)
insert into cb (itime,ityp,ivalue)values('2007-09-11 16:00:00.000','B',64)
insert into cb (itime,ityp,ivalue)values('2007-05-11 16:00:00.000','C',71)
insert into cb (itime,ityp,ivalue)values('2007-09-11 16:00:00.000','D',78)
insert into cb (itime,ityp,ivalue)values('2007-09-11 16:00:00.000','E',76)select
itime,
A=max(case ityp when 'A' then ivalue else 0 end),
B=max(case ityp when 'B' then ivalue else 0 end),
C=max(case ityp when 'C' then ivalue else 0 end),
D=max(case ityp when 'D' then ivalue else 0 end),
E=max(case ityp when 'E' then ivalue else 0 end)
from
cb
group by itime
order by itime
select
itime,
case ityp when 'A' then ivalue else 0 end as A,
case ityp when 'B' then ivalue else 0 end as B,
case ityp when 'C' then ivalue else 0 end as C,
case ityp when 'D' then ivalue else 0 end as D,
case ityp when 'E' then ivalue else 0 end as E
from
cb
group by itime ,ityp,ivalue
order by itime