编号 活动ID 内容 工号 时间
……
CAA-0701001 120 请速处理 11110052 2007-01-17 11:00:00
CAA-0701001 120 金额未核算 11110052 2007-01-17 14:56:00
CAA-0701001 120 OK 11110052 2007-01-18 08:54:00
CAA-0701001 130 OK 11110656 2007-01-17 11:00:00
CAA-0701001 130 N/G 11110656 2007-01-18 08:58:00
……
怎样把以上的记录变成
1,横向排列,
2,取最近时间的记录,如活动ID为120的最近一笔是2007-01-18 08:54:00的记录
3,活动ID是从110~210,表里还没有写进来的记录如140没有,它的内容就设为空的
如:
编号 120内容 工号 130内容 工号 140内容 工号……
CAA-0701001 OK 11110052 N/G 11110656 null null
原來的做法是:
select
ManagementID,
A01 = max(case ActiveID when 110 then TreatmentDescription else '' end), A110ID = max(case ActiveID when 110 then UserID else '' end), A110D= max(case ActiveID when 110 then TreatDate else '' end),
A02 = max(case ActiveID when 120 then TreatmentDescription else '' end), A120ID = max(case ActiveID when 120 then UserID else '' end),A120D= max(case ActiveID when 120 then TreatDate else '' end),
A03 = max(case ActiveID when 130 then TreatmentDescription else '' end), A130ID = max(case ActiveID when 130 then UserID else '' end),A130D= max(case ActiveID when 130 then TreatDate else '' end),
A05 = max(case ActiveID when 150 then TreatmentDescription else '' end),A150ID = max(case ActiveID when 150 then UserID else '' end),A150D= max(case ActiveID when 150 then TreatDate else '' end),
A06 = max(case ActiveID when 160 then TreatmentDescription else '' end), A160ID = max(case ActiveID when 160 then UserID else '' end),A160D= max(case ActiveID when 160 then TreatDate else '' end),
A07 = max(case ActiveID when 170 then TreatmentDescription else '' end), A170ID = max(case ActiveID when 170 then UserID else '' end),A170D= max(case ActiveID when 170 then TreatDate else '' end),
A08 = max(case ActiveID when 180 then TreatmentDescription else '' end),A180ID = max(case ActiveID when 180 then UserID else '' end),A180D= max(case ActiveID when 180 then TreatDate else '' end),
A09 = max(case ActiveID when 190 then TreatmentDescription else '' end),A190ID = max(case ActiveID when 190 then UserID else '' end),A190D= max(case ActiveID when 190 then TreatDate else '' end),
A10 = max(case ActiveID when 200 then TreatmentDescription else '' end),EndID = max(case ActiveID when 200 then UserID else '' end),EndD= max(case ActiveID when 200 then TreatDate else '' end)
from
complainnoteTreatment
group by
ManagementID
現在看來,不行了,有點亂!請大家指教!謝謝!
……
CAA-0701001 120 请速处理 11110052 2007-01-17 11:00:00
CAA-0701001 120 金额未核算 11110052 2007-01-17 14:56:00
CAA-0701001 120 OK 11110052 2007-01-18 08:54:00
CAA-0701001 130 OK 11110656 2007-01-17 11:00:00
CAA-0701001 130 N/G 11110656 2007-01-18 08:58:00
……
怎样把以上的记录变成
1,横向排列,
2,取最近时间的记录,如活动ID为120的最近一笔是2007-01-18 08:54:00的记录
3,活动ID是从110~210,表里还没有写进来的记录如140没有,它的内容就设为空的
如:
编号 120内容 工号 130内容 工号 140内容 工号……
CAA-0701001 OK 11110052 N/G 11110656 null null
原來的做法是:
select
ManagementID,
A01 = max(case ActiveID when 110 then TreatmentDescription else '' end), A110ID = max(case ActiveID when 110 then UserID else '' end), A110D= max(case ActiveID when 110 then TreatDate else '' end),
A02 = max(case ActiveID when 120 then TreatmentDescription else '' end), A120ID = max(case ActiveID when 120 then UserID else '' end),A120D= max(case ActiveID when 120 then TreatDate else '' end),
A03 = max(case ActiveID when 130 then TreatmentDescription else '' end), A130ID = max(case ActiveID when 130 then UserID else '' end),A130D= max(case ActiveID when 130 then TreatDate else '' end),
A05 = max(case ActiveID when 150 then TreatmentDescription else '' end),A150ID = max(case ActiveID when 150 then UserID else '' end),A150D= max(case ActiveID when 150 then TreatDate else '' end),
A06 = max(case ActiveID when 160 then TreatmentDescription else '' end), A160ID = max(case ActiveID when 160 then UserID else '' end),A160D= max(case ActiveID when 160 then TreatDate else '' end),
A07 = max(case ActiveID when 170 then TreatmentDescription else '' end), A170ID = max(case ActiveID when 170 then UserID else '' end),A170D= max(case ActiveID when 170 then TreatDate else '' end),
A08 = max(case ActiveID when 180 then TreatmentDescription else '' end),A180ID = max(case ActiveID when 180 then UserID else '' end),A180D= max(case ActiveID when 180 then TreatDate else '' end),
A09 = max(case ActiveID when 190 then TreatmentDescription else '' end),A190ID = max(case ActiveID when 190 then UserID else '' end),A190D= max(case ActiveID when 190 then TreatDate else '' end),
A10 = max(case ActiveID when 200 then TreatmentDescription else '' end),EndID = max(case ActiveID when 200 then UserID else '' end),EndD= max(case ActiveID when 200 then TreatDate else '' end)
from
complainnoteTreatment
group by
ManagementID
現在看來,不行了,有點亂!請大家指教!謝謝!
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货