!!!!!!求SQL语句,行转列问题.....结贴超快,先到先得,顶者有分!
!!!!!!求SQL语句,结贴超快,先到先得,顶者有分!
表TabA
IDEmpIDRegDateItemNameRValue
------------------------
74506050292007-03-01基本工资.000000
74606050292007-03-01计时工资.000000
74706050292007-03-01计件工资.000000
74806050292007-03-01提成工资.000000
75406050292007-03-01通宵津贴.000000
75506050292007-03-01住宿津贴.000000
75606050292007-03-01 交通津贴.000000
75706050292007-03-01水电津贴.000000
75806050292007-03-01社保津贴.000000
75906050292007-03-01 医保津贴.000000
76006050292007-03-01补上期.000000
76106050292007-03-01扣上期.000000
76206050292007-03-01扣迟到.000000
76306050292007-03-01扣早退.000000
76406050292007-03-01扣旷工.000000
76506050292007-03-01 扣住宿费.000000
76606050292007-03-01扣水电费.000000
76706050292007-03-01 扣社保费.000000
76806050292007-03-01 扣医保费.000000
76906050292007-03-01扣所得税.000000
77006050292007-03-01应发.000000
77106050292007-03-01实发.000000
77206050302007-03-01基本工资.000000
77306050302007-03-01计时工资.000000
77406050302007-03-01计件工资.000000
77506050302007-03-01提成工资.000000
77606050302007-03-01通宵津贴.000000
77706050302007-03-01住宿津贴.000000
77806050302007-03-01 交通津贴.000000
77906050302007-03-01水电津贴.000000
78006050302007-03-01社保津贴.000000
78106050302007-03-01 医保津贴.000000
78206050302007-03-01补上期.000000
78306050302007-03-01扣上期.000000
78406050302007-03-01扣迟到.000000
78506050302007-03-01扣早退.000000
78606050302007-03-01扣旷工.000000
78706050302007-03-01 扣住宿费.000000
78806050302007-03-01扣水电费.000000
78906050302007-03-01 扣社保费.000000
79006050302007-03-01 扣医保费.000000
79106050302007-03-01扣所得税.000000
79206050302007-03-01应发.000000
79306050302007-03-01实发.000000
需要用SQL语句转换为下表格式
( 假设最列宽为5列,员工编号和日期在后面行不需要重复)
EmpIDRegDate i1 i2 i3
----------------------------
06050292007-03-01基本工资计时工资计件工资
.000000.000000.000000
提成工资通宵津贴住宿津贴
.000000.000000.000000
.......直到ItemName排列完成
06050302007-03-01基本工资计时工资计件工资
.000000.000000.000000
提成工资通宵津贴住宿津贴
.000000.000000.000000
.......直到ItemName排列完成
[解决办法]
declare @sql varchar(8000)
set @sql = 'select EmpID,RegDate '
select @sql = @sql + ' , sum(case ItemName when ' ' ' + ItemName + ' ' ' then RValue else 0 end) [ ' + ItemName + '] '
from (select distinct RValue from tb) as a
set @sql = @sql + ' from tb group by EmpID,RegDate '
exec(@sql)
[解决办法]
我慢,我先顶还不行?????????
[解决办法]
jf
[解决办法]
sql service不支持交叉查询,在程序中很方便的
