!!!!!!求SQL语句,行转列问题.....结贴超快,先到先得,顶者有分!
!!!!!!求SQL语句,结贴超快,先到先得,顶者有分!
表TabA
ID EmpID RegDate ItemName RValue
------------------------
745 0605029 2007-03-01 基本工资 .000000
746 0605029 2007-03-01 计时工资 .000000
747 0605029 2007-03-01 计件工资 .000000
748 0605029 2007-03-01 提成工资 .000000
754 0605029 2007-03-01 通宵津贴 .000000
755 0605029 2007-03-01 住宿津贴 .000000
756 0605029 2007-03-01 交通津贴 .000000
757 0605029 2007-03-01 水电津贴 .000000
758 0605029 2007-03-01 社保津贴 .000000
759 0605029 2007-03-01 医保津贴 .000000
760 0605029 2007-03-01 补上期 .000000
761 0605029 2007-03-01 扣上期 .000000
762 0605029 2007-03-01 扣迟到 .000000
763 0605029 2007-03-01 扣早退 .000000
764 0605029 2007-03-01 扣旷工 .000000
765 0605029 2007-03-01 扣住宿费 .000000
766 0605029 2007-03-01 扣水电费 .000000
767 0605029 2007-03-01 扣社保费 .000000
768 0605029 2007-03-01 扣医保费 .000000
769 0605029 2007-03-01 扣所得税 .000000
770 0605029 2007-03-01 应发 .000000
771 0605029 2007-03-01 实发 .000000
772 0605030 2007-03-01 基本工资 .000000
773 0605030 2007-03-01 计时工资 .000000
774 0605030 2007-03-01 计件工资 .000000
775 0605030 2007-03-01 提成工资 .000000
776 0605030 2007-03-01 通宵津贴 .000000
777 0605030 2007-03-01 住宿津贴 .000000
778 0605030 2007-03-01 交通津贴 .000000
779 0605030 2007-03-01 水电津贴 .000000
780 0605030 2007-03-01 社保津贴 .000000
781 0605030 2007-03-01 医保津贴 .000000
782 0605030 2007-03-01 补上期 .000000
783 0605030 2007-03-01 扣上期 .000000
784 0605030 2007-03-01 扣迟到 .000000
785 0605030 2007-03-01 扣早退 .000000
786 0605030 2007-03-01 扣旷工 .000000
787 0605030 2007-03-01 扣住宿费 .000000
788 0605030 2007-03-01 扣水电费 .000000
789 0605030 2007-03-01 扣社保费 .000000
790 0605030 2007-03-01 扣医保费 .000000
791 0605030 2007-03-01 扣所得税 .000000
792 0605030 2007-03-01 应发 .000000
793 0605030 2007-03-01 实发 .000000
需要用SQL语句转换为下表格式
( 假设最列宽为5列,员工编号和日期在后面行不需要重复)
EmpID RegDate i1 i2 i3
----------------------------
0605029 2007-03-01 基本工资 计时工资 计件工资
.000000 .000000 .000000
提成工资 通宵津贴 住宿津贴
.000000 .000000 .000000
.......直到ItemName排列完成
0605030 2007-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不支持交叉查询,在程序中很方便的