W表数据如下:
id code mydate ncode name text flag 70 2008052601 2008-5-26 wangf 王风 进度准备 1
74 2008052601 2008-5-26 wangt 王同 还可以 2
75 2008052601 2008-5-26 qt 齐天 等等 0
76 2008052602 2005-5-27 sd 孙都 开始 0
--------------------------------------------------------------
想做成交叉表,多行并成一行,以code为分组条件在mydate的日期上合并并形成三列(flag只能是0,1,2)
如此 2008052601 2008-5-26 0 qt 1 王风 2 王同
如果不全则显示无: 2008052701 2008-5-27 0 sd 1 无 2 无
id code mydate ncode name text flag 70 2008052601 2008-5-26 wangf 王风 进度准备 1
74 2008052601 2008-5-26 wangt 王同 还可以 2
75 2008052601 2008-5-26 qt 齐天 等等 0
76 2008052602 2005-5-27 sd 孙都 开始 0
--------------------------------------------------------------
想做成交叉表,多行并成一行,以code为分组条件在mydate的日期上合并并形成三列(flag只能是0,1,2)
如此 2008052601 2008-5-26 0 qt 1 王风 2 王同
如果不全则显示无: 2008052701 2008-5-27 0 sd 1 无 2 无
你希望如何处理呢?
0 [0],
max(case flag when 0 then ncode else null end) [0_1],
1 [1],
max(case flag when 1 then name else '无' end) [1_1],
2 [2],
max(case flag when 0 then name else '无' end) [2_1]
from tb
group by code,mydate
Insert into @W
select 70,2008052601,'2008-5-26','wangf','王风','进度准备',1 union all
select 74,2008052601,'2008-5-26','wangt','王同','还可以',2 union all
select 75,2008052601,'2008-5-26','qt','齐天','等等',0 union all
select 76,2008052602,'2005-5-27','sd','孙都','开始',0
Select code,mydate,
max(case when flag =0 then [name] else ''end),
0,
max( case when flag =1 then [name] else '' end),
1,
max(case when flag=2 then [name]else ''end),
2
from @W
group by code,mydate
/*
code mydate
-------------------- -------------------- -------------------- ----------- -------------------- ----------- -------------------- -----------
2008052602 2005-5-27 孙都 0 1 2
2008052601 2008-5-26 齐天 0 王风 1 王同 2(2 row(s) affected)*/