select Service, shop, DateIn,
CASE type WHEN '常规单' THEN Much ELSE 0 END AS '常规单',
CASE type WHEN '开卡' THEN Much ELSE 0 END AS '开卡',
CASE type WHEN '会员' THEN Much ELSE 0 END AS '会员',
......
from tableName order by DateIn
CASE type WHEN '常规单' THEN Much ELSE 0 END AS '常规单',
CASE type WHEN '开卡' THEN Much ELSE 0 END AS '开卡',
CASE type WHEN '会员' THEN Much ELSE 0 END AS '会员',
......
from tableName order by DateIn
set @s = ''
select @s = ','+type+'=sum(case when type='''+type+''' then Much e;se 0 end)'
from (select distinct type from 表)set @s = 'select Service,shop,DateIn'+@s+' from 表 group by Service,shop,DateIn'
exec(@s)
create table #t(Service varchar(10),shop int,DateIn datetime,type varchar(10),Much int)
insert into #t select 'MeiFa',101,'2005-07-28','常规单',1
insert into #t select 'MeiFa',101,'2005-07-28','开卡',4
insert into #t select 'MeiFa',101,'2005-07-28','会员',6
insert into #t select 'MeiFa',101,'2005-07-28','新客',2
insert into #t select 'MeiFa',101,'2005-07-26','新客',2
insert into #t select 'MeiFa',101,'2005-07-26','会员',1
insert into #t select 'MeiFa',101,'2005-07-26','开卡',4
insert into #t select 'MeiFa',101,'2005-07-26','常规单',10
insert into #t select 'MeiFa',101,'2005-07-27','新客',32
insert into #t select 'MeiFa',101,'2005-07-27','会员',6
insert into #t select 'MeiFa',101,'2005-07-27','开卡',8
insert into #t select 'MeiFa',101,'2005-07-27','常规单',9
insert into #t select 'MeiFa',101,'2005-07-27','退货',20
insert into #t select 'MeiFa',101,'2005-07-27','投诉',7
insert into #t select 'MeiFa',101,'2005-07-27','其它',3
--交叉表查询
declare @s varchar(8000)
set @s = ''
select @s = ','+type+'=sum(case when type='''+type+''' then Much e;se 0 end)'
from (select distinct type from #t)set @s = 'select Service,shop,DateIn'+@s+' from #t group by Service,shop,DateIn'
exec(@s)
--输出结果
Service shop DateIn 常规单 会员 开卡 其它 投诉 退货 新客
------- ---- ---------- ------ ---- ---- ---- ---- ---- ----
MeiFa 101 2005-07-26 10 1 4 0 0 0 2
MeiFa 101 2005-07-27 9 6 8 3 7 20 32
MeiFa 101 2005-07-28 1 6 4 0 0 0 2
CASE type WHEN '常规单' THEN Much ELSE 0 END AS '常规单',
CASE type WHEN '开卡' THEN Much ELSE 0 END AS '开卡',
CASE type WHEN '会员' THEN Much ELSE 0 END AS '会员',
......
from tableName order by DateIn
-----这个的type数量应该是确定的,直接全部写出来就行