DataSet实现行转列 AnyLine

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