交叉表简单例子

Create table T(名称 varchar(20),日期 datetime,时间 varchar(20),售货金额 int)
insert into T select 'abc','2006-01-02','早上',50
union all select 'abc','2006-01-02','中午',20
union all select 'abc','2006-01-02','晚上',30
union all select 'abc','2006-01-02','零晨',40
union all select 'abc','2006-01-03','早上',40
union all select 'abc','2006-01-03','中午',60
union all select 'abc','2006-01-03','晚上',50
union all select 'abc','2006-01-03','零晨',50
union all select 'abc','2006-01-04','早上',80
union all select 'abc','2006-01-04','中午',60
union all select 'abc','2006-01-04','晚上',20
union all select 'abc','2006-01-04','零晨',40
-----------------------------------------------------------
select * from t


select * ,金额小计=(select sum(售货金额) from T where 日期=PT.日期 ) from T as TAB
PIVOT ( max(售货金额) for 时间 in ([早上],[中午],[晚上],[零晨])
      ) as PT

 交叉前的表

交叉后的表

 

转载于:https://www.cnblogs.com/sapb1/p/8476950.html