问题描述
工资编号员工编号工资项工资部门编号11岗位工资1000111薪级工资2000111取暖费1201..22岗位工资2000122薪级工资100122取暖费300133岗位工资4000233薪级工资211233取暖费3002....--------------------------------------工资项是不固定的人员不固定部门不固定---------------------------------------我想变成。。。员工编号部门编号岗位工资薪级工资取暖费.......11100020001202110002000120小计20004000240322000100300小计2000100300.........-----------------------------------------------------我现在已经可以做出来员工编号岗位工资薪级工资取暖费.......11000200012021000200012032000100300小计40004100540------------------------------------------------但是加上安类别分开就不行了希望高手能解答下刚注册的号有多少分给多少。。。
解决方案
解决方案二：
学习。。。。。。。。。。。
解决方案三：
zen怎么没人回答啊 大侠们救命啊．．．．
解决方案四：
下面是我的存储过程 然后利用ＧＲＩＤＶＩＥＷ主详表来显示 但是没个部门统计无法实现 ．．．．．．．．．CREATEPROCEDUREgetzhucongASselect*fromygleftjoindepartmentonyg.d_id=department.d_idorderbyyg.d_idascdeclare@sqlvarchar(8000)set@sql='selectconvert(int,shifa.gzbh)as工资编号,yg.yg_nameas员工姓名,shifa.yg_id,d_name'select@sql=@sql+',Max(casesubstring(shifa.gzx,3,20)when'''+substring(gzx,3,20)+'''thenconvert(numeric(19,2),shifa.gz)else0end)['+substring(gzx,3,20)+']'from(selectdistinctgzxfromshifa)asaselect@sql=@sql+',Max(caseykf.gzxwhen'''+gzx+'''thenconvert(numeric(19,2),ykf.gz)else0end)['+gzx+']'from(selectdistinctgzxfromykf)ascselect@sql=@sql+',Max(casesubstring(yingkou.gzx,3,20)when'''+substring(gzx,3,20)+'''thenconvert(numeric(19,2),yingkou.gz)else0end)['+substring(gzx,3,20)+']'from(selectdistinctgzxfromyingkou)asbselect@sql=@sql+',Max(casesubstring(yks.gzx,3,20)when'''+substring(gzx,3,20)+'''thenconvert(numeric(19,2),yks.gz)else0end)['+substring(gzx,3,20)+']'from(selectdistinctgzxfromyks)asdselect@sql=@sql+',Max(casesubstring(gz_zj.gzx,3,20)when'''+substring(gzx,3,20)+'''thenconvert(numeric(19,2),gz_zj.gz)else0end)['+substring(gzx,3,20)+']'from(selectdistinctgzxfromgz_zj)aseset@sql=@sql+'fromshifaleftjoindepartmentonshifa.bumen=department.d_idleftjoingz_zjonshifa.gzbh=gz_zj.gzbhleftjoinyingkouonshifa.gzbh=yingkou.gzbhleftjoinykfonykf.gzbh=shifa.gzbhleftjoinygonyg.yg_id=shifa.yg_idleftjoinyksonshifa.gzbh=yks.gzbhgroupbyd_name,shifa.bumen,shifa.gzbh,yg_name,shifa.yg_idorderbyshifa.bumenasc'exec(@sql)GO
解决方案五：
if(exists(select1fromsys.objectswhereobject_id=OBJECT_ID(N'dbo.WageInfo')andtypein(N'U')))droptableWageInfocreatetableWageInfo(工资编号int,员工编号int,工资项varchar(40),工资decimal(10,2),部门编号int)insertintoWageinfoselect1,1,N'岗位工资',1000,1unionallselect1,1,N'薪级工资',2000,1unionallselect1,1,N'取暖费',120,1unionallselect2,2,N'岗位工资',2000,1unionallselect2,2,N'薪级工资',100,1unionallselect2,2,N'取暖费',300,1unionallselect3,3,N'岗位工资',4000,2unionallselect3,3,N'薪级工资',211,2unionallselect3,3,N'取暖费',300,2select*fromwageinfoBEGINDECLARE@VALUEVARCHAR(3000)SELECT@VALUE='select工资编号'SELECT@VALUE=@VALUE+',max(case工资项when'''+工资项+'''then工资else0end)['+工资项+']'from(selectdistinct工资项fromWageinfo)asaselect@VALUE=@VALUE+'fromWageinfogroupby工资编号'exec(@value)END
解决方案六：
结果--11000.00120.002000.00--22000.00300.00100.00--34000.00300.00211.00
解决方案七：
这个方法我用过了可以实现主从表关系但是没有按分组统计。。。。。。。。
解决方案八：
搜索"交叉表"回复内容太短了！