一个行转列问题,太久不用了,脑子木,高手帮忙啊
pam_payinfo_detail 是薪资明细表
Sys_Users 是员工表
selectB.name,
'attend_days1 ' = (case A.month when '01 ' then sum(isnull(attend_days,0)) else 0 end),
'attend_days2 ' = (case A.month when '02 ' then sum(isnull(attend_days,0)) else 0 end),
'attend_days3 ' = (case A.month when '03 ' then sum(isnull(attend_days,0)) else 0 end),
'attend_days4 ' = (case A.month when '04 ' then sum(isnull(attend_days,0)) else 0 end),
'attend_days5 ' = (case A.month when '05 ' then sum(isnull(attend_days,0)) else 0 end),
'attend_days6 ' = (case A.month when '06 ' then sum(isnull(attend_days,0)) else 0 end),
'attend_days7 ' = (case A.month when '07 ' then sum(isnull(attend_days,0)) else 0 end),
'attend_days8 ' = (case A.month when '08 ' then sum(isnull(attend_days,0)) else 0 end),
'attend_days9 ' = (case A.month when '09 ' then sum(isnull(attend_days,0)) else 0 end),
'attend_days10 ' = (case A.month when '10 ' then sum(isnull(attend_days,0)) else 0 end),
'attend_days11 ' = (case A.month when '11 ' then sum(isnull(attend_days,0)) else 0 end),
'attend_days12 ' = (case A.month when '12 ' then sum(isnull(attend_days,0)) else 0 end)
frompam_payinfo_detail A
joinSys_Users BOn A.userid = B.userid
Group BYA.userid,
B.name,
C.Year_month
这是想实现
姓名 一月,二月,三月,四月....这种效果,但很明显我的语句是检查不过去的
报告:选择列表中的列 'pam_payinfo_detail.month ' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。
请高手帮帮忙吧没分了,谢谢谢谢谢谢谢谢
[解决办法]
-- sum 及 isnull 放在 case when 外面
'attend_days_xx ' = sum(isnull(case A.month when 'xx ' then attend_days,0 else 0 end),
[解决办法]
try
selectB.name,
'attend_days1 ' = sum(case A.[month] when '01 ' then isnull(attend_days,0) else 0 end),
'attend_days2 ' = sum(case A.[month] when '02 ' then isnull(attend_days,0) else 0 end),
'attend_days3 ' = sum(case A.[month] when '03 ' then isnull(attend_days,0) else 0 end),
'attend_days4 ' = sum(case A.[month] when '04 ' then isnull(attend_days,0) else 0 end),
'attend_days5 ' = sum(case A.[month] when '05 ' then isnull(attend_days,0) else 0 end),
'attend_days6 ' = sum(case A.[month] when '06 ' then isnull(attend_days,0) else 0 end),
'attend_days7 ' = sum(case A.[month] when '07 ' then isnull(attend_days,0) else 0 end),
'attend_days8 ' = sum(case A.[month] when '08 ' then isnull(attend_days,0) else 0 end),
'attend_days9 ' = sum(case A.[month] when '09 ' then isnull(attend_days,0) else 0 end),
'attend_days10 ' = sum(case A.[month] when '10 ' then isnull(attend_days,0) else 0 end),
'attend_days11 ' = sum(case A.[month] when '11 ' then isnull(attend_days,0) else 0 end),
'attend_days12 ' = sum(case A.[month] when '12 ' then isnull(attend_days,0) else 0 end)
frompam_payinfo_detail A
joinSys_Users BOn A.userid = B.userid
Group BYA.userid,
B.name,
A.[month]
[解决办法]
isnull可以放在裡面，但是sum最好放在外面.
