DataSet实现行转列

最后更新:2023-10-18 21:13:51 | 状态:未完成
DataSet set = service.querys("V_HR_SALARY","YYYY:"+ yyyy, "ORDER BY YM");
DataSet groups = set.pivot("EMPLOYEE_CODE,EMPLOYEE_NM","YM","TOTAL_PRICE");
//EMPLOYEE_CODE,EMPLOYEE_NM 作为主键,决定结果有多少行
//YM 转换维度,决定结果有多少列,把月份作为列,有多少月份就有多少列
//TOTAL_PRICE 取值

原数据(240行-20个人12个月)

序号 工号 姓名 月份 底薪 奖金 合计
1 1005 练霓裳 2020-08 11000.00 200.00 10600.00
2 1005 练霓裳 2020-08 11000.00 200.00 10600.00
3 1005 练霓裳 2020-08 11000.00 200.00 10600.00
4 1005 练霓裳 2020-08 11000.00 200.00 10600.00
5 1005 练霓裳 2020-08 11000.00 200.00 10600.00
6 1005 练霓裳 2020-08 11000.00 200.00 10600.00
7 1005 练霓裳 2020-08 11000.00 200.00 10600.00
8 1005 练霓裳 2020-08 11000.00 200.00 10600.00
9 1005 练霓裳 2020-08 11000.00 200.00 10600.00
10 1005 练霓裳 2020-08 11000.00 200.00 10600.00

转换结果(20行-20个人,12列-12个月)

工号 姓名 2020-01 2020-02 2020-03 2020-04 2020-05 2020-06 2020-07 2020-08 2020-09 2020-10 2020-11 2020-12 小计(人)
1001 风清扬 19,400.00 22,600.00 17,100.00 29,000.00 15,900.00 22,700.00 14,900.00 17,500.00 27,000.00 20,100.00 28,100.00 20,900.00 255,200.00
1007 邀月 22,300.00 21,800.00 17,100.00 22,800.00 20,500.00 27,200.00 29,300.00 25,200.00 19,000.00 25,600.00 28,600.00 26,300.00 285,700.00
1011 黄药师 23,800.00 26,900.00 16,500.00 26,300.00 16,400.00 29,000.00 14,400.00 22,400.00 21,400.00 20,600.00 20,100.00 28,300.00 266,100.00
1002 步惊云 15,800.00 18,800.00 23,000.00 13,700.00 20,500.00 24,600.00 12,100.00 27,200.00 31,300.00 13,800.00 12,000.00 13,500.00 226,300.00
1003 李寻欢 27,700.00 20,000.00 14,700.00 21,300.00 19,000.00 24,300.00 27,700.00 24,900.00 16,700.00 14,100.00 23,100.00 17,800.00 251,300.00
1015 燕十三 15,200.00 18,500.00 15,900.00 13,400.00 30,700.00 22,000.00 28,600.00 27,500.00 14,000.00 14,600.00 19,200.00 28,600.00 248,200.00
1008 王语嫣 14,800.00 28,400.00 27,400.00 27,800.00 19,000.00 24,400.00 29,700.00 24,500.00 19,700.00 14,300.00 27,700.00 23,300.00 281,000.00
1006 丁春秋 16,000.00 33,600.00 18,200.00 23,100.00 24,300.00 15,400.00 32,800.00 21,000.00 24,000.00 13,200.00 27,400.00 17,600.00 266,600.00
1013 任我行 22,600.00 30,800.00 27,700.00 11,700.00 33,400.00 10,200.00 10,800.00 18,800.00 19,600.00 15,600.00 21,400.00 31,700.00 254,300.00
1019 张无忌 17,400.00 12,900.00 13,100.00 19,600.00 16,300.00 30,200.00 16,300.00 18,600.00 15,600.00 26,300.00 22,100.00 26,200.00 234,600.00
1009 上官无极 30,200.00 29,300.00 11,500.00 29,900.00 28,400.00 22,700.00 30,800.00 19,500.00 15,100.00 31,100.00 21,900.00 26,200.00 296,600.00
1018 慕容复 24,700.00 18,500.00 15,900.00 26,800.00 14,600.00 15,400.00 19,800.00 9,800.00 19,600.00 29,600.00 14,700.00 24,300.00 233,700.00
1017 周芷若 30,900.00 15,700.00 13,500.00 14,500.00 30,100.00 27,700.00 26,500.00 15,400.00 29,700.00 14,900.00 31,500.00 19,800.00 270,200.00
1014 李莫愁 13,800.00 14,800.00 17,600.00 16,100.00 24,700.00 21,500.00 25,000.00 16,500.00 25,800.00 18,800.00 24,500.00 15,100.00 234,200.00
1004 西门吹雪 21,300.00 28,500.00 14,800.00 18,200.00 21,500.00 25,200.00 29,200.00 27,100.00 18,300.00 16,900.00 28,100.00 18,300.00 267,400.00
1016 燕南天 20,800.00 21,700.00 13,700.00 17,000.00 18,300.00 11,900.00 27,300.00 29,400.00 11,000.00 24,500.00 32,800.00 28,800.00 257,200.00
1005 练霓裳 23,000.00 19,500.00 15,000.00 27,200.00 33,700.00 32,100.00 13,600.00 17,700.00 19,600.00 33,000.00 24,600.00 19,300.00 278,300.00
1012 令狐冲 13,500.00 22,100.00 22,900.00 20,200.00 22,500.00 12,600.00 33,200.00 15,700.00 12,300.00 24,200.00 23,100.00 21,000.00 243,300.00
1010 欧阳锋 18,700.00 15,900.00 27,400.00 26,500.00 31,200.00 14,500.00 31,400.00 14,700.00 27,200.00 27,800.00 20,000.00 22,000.00 277,300.00
1020 小龙女 31,000.00 18,500.00 16,900.00 17,900.00 14,800.00 28,300.00 21,400.00 26,200.00 22,800.00 26,800.00 26,800.00 21,000.00 272,400.00
小计(月) 422,900.00 438,800.00 359,900.00 423,000.00 455,800.00 441,900.00 474,800.00 419,600.00 409,700.00 425,800.00 477,700.00 450,000.00 5,199,900.00
首页 最近更新 搜索 提交 回复