22,209
社区成员
发帖
与我相关
我的任务
分享
select
t1.TaxCalculationID,
t1.EmployeeID,
t1.TaxTime,
t1.SalaryGroupID,
t2.EmployeeCode,
t2.EmployeeName,
--状态 0:草稿, 1:已提交, 2:已审核
(CASE
WHEN taxEnsur.RecordStatus=1 THEN '草稿'
WHEN taxEnsur.RecordStatus=2 THEN '已提交'
ELSE '草稿' END) AS RecordStatusName,
t3.TaxItemValue as item1010,
t4.TaxItemValue as item1020,
t5.TaxItemValue as item1030,
t6.TaxItemValue as item1040,
t7.TaxItemValue as item1050,
t8.TaxItemValue as item1061,
t9.TaxItemValue as item1070,
t10.TaxItemValue as item1080,
t11.TaxItemValue as item1090,
t12.TaxItemValue as item1100,
t13.TaxItemValue as item1110,
t14.TaxItemValue as item1120,
t15.TaxItemValue as item1270,
t16.TaxItemValue as item1140,
t17.TaxItemValue as item1150,
t18.TaxItemValue as item1160,
t19.TaxItemValue as item1170,
t20.TaxItemValue as item1180,
t21.TaxItemValue as item1190,
t22.TaxItemValue as item1200,
t23.TaxItemValue as item1210,
t24.TaxItemValue as item1220,
t25.TaxItemValue as item1230,
t26.TaxItemValue as item1240,
t27.TaxItemValue as item1250,
t28.TaxItemValue as item1260,
t29.TaxItemValue as item1226
from dbo.RP_TaxCalculation t1
left join dbo.RP_TaxEnsure taxEnsur
ON taxEnsur.SalaryGroupID = t1.SalaryGroupID
AND taxEnsur.TaxTime = t1.TaxTime
left join HR_Employee t2
ON t1.EmployeeID=t2.EmployeeID
left join RP_TaxCalculationItemResult t3 on t3.TaxCalculationID=t1.TaxCalculationID and t3.SalaryItemCode='item1010'--上月工资
left join RP_TaxCalculationItemResult t4 on t4.TaxCalculationID=t1.TaxCalculationID and t4.SalaryItemCode='item1020'--上月养老保险
left join RP_TaxCalculationItemResult t5 on t5.TaxCalculationID=t1.TaxCalculationID and t5.SalaryItemCode='item1030'--上月医疗保险
left join RP_TaxCalculationItemResult t6 on t6.TaxCalculationID=t1.TaxCalculationID and t6.SalaryItemCode='item1040'--上月失业保险
left join RP_TaxCalculationItemResult t7 on t7.TaxCalculationID=t1.TaxCalculationID and t7.SalaryItemCode='item1050'--上月公积金
left join RP_TaxCalculationItemResult t8 on t8.TaxCalculationID=t1.TaxCalculationID and t8.SalaryItemCode='item1061'--上月公积金差额
left join RP_TaxCalculationItemResult t9 on t9.TaxCalculationID=t1.TaxCalculationID and t9.SalaryItemCode='item1070'--上月商业健康保险
left join RP_TaxCalculationItemResult t10 on t10.TaxCalculationID=t1.TaxCalculationID and t10.SalaryItemCode='item1080'--上月应纳税工资合计
left join RP_TaxCalculationItemResult t11 on t11.TaxCalculationID=t1.TaxCalculationID and t11.SalaryItemCode='item1090'--安质奖
left join RP_TaxCalculationItemResult t12 on t12.TaxCalculationID=t1.TaxCalculationID and t12.SalaryItemCode='item1100'--业代津贴
left join RP_TaxCalculationItemResult t13 on t13.TaxCalculationID=t1.TaxCalculationID and t13.SalaryItemCode='item1110'--司机补贴
left join RP_TaxCalculationItemResult t14 on t14.TaxCalculationID=t1.TaxCalculationID and t14.SalaryItemCode='item1120'--其它
left join RP_TaxCalculationItemResult t15 on t15.TaxCalculationID=t1.TaxCalculationID and t15.SalaryItemCode='item1270'--本月其它合计
left join RP_TaxCalculationItemResult t16 on t16.TaxCalculationID=t1.TaxCalculationID and t16.SalaryItemCode='item1140'--应税工资合计
left join RP_TaxCalculationItemResult t17 on t17.TaxCalculationID=t1.TaxCalculationID and t17.SalaryItemCode='item1150'--累计收入
left join RP_TaxCalculationItemResult t18 on t18.TaxCalculationID=t1.TaxCalculationID and t18.SalaryItemCode='item1160'--累计养老保险
left join RP_TaxCalculationItemResult t19 on t19.TaxCalculationID=t1.TaxCalculationID and t19.SalaryItemCode='item1170'--累计医疗保险
left join RP_TaxCalculationItemResult t20 on t20.TaxCalculationID=t1.TaxCalculationID and t20.SalaryItemCode='item1180'--累计失业保险
left join RP_TaxCalculationItemResult t21 on t21.TaxCalculationID=t1.TaxCalculationID and t21.SalaryItemCode='item1190'--累计公积金
left join RP_TaxCalculationItemResult t22 on t22.TaxCalculationID=t1.TaxCalculationID and t22.SalaryItemCode='item1200'--累计公积金差额
left join RP_TaxCalculationItemResult t23 on t23.TaxCalculationID=t1.TaxCalculationID and t23.SalaryItemCode='item1210'--累计专项附加扣除
left join RP_TaxCalculationItemResult t24 on t24.TaxCalculationID=t1.TaxCalculationID and t24.SalaryItemCode='item1220'--累计商业健康保险
left join RP_TaxCalculationItemResult t25 on t25.TaxCalculationID=t1.TaxCalculationID and t25.SalaryItemCode='item1230'--累计减除费用
left join RP_TaxCalculationItemResult t26 on t26.TaxCalculationID=t1.TaxCalculationID and t26.SalaryItemCode='item1240'--累计应纳税额
left join RP_TaxCalculationItemResult t27 on t27.TaxCalculationID=t1.TaxCalculationID and t27.SalaryItemCode='item1250'--累计已缴个税
left join RP_TaxCalculationItemResult t28 on t28.TaxCalculationID=t1.TaxCalculationID and t28.SalaryItemCode='item1260'--本月个税
left join RP_TaxCalculationItemResult t29 on t29.TaxCalculationID=t1.TaxCalculationID and t29.SalaryItemCode='item1226'--累计应纳税所得额
where 1=1
AND t1.SalaryGroupID = 'DEA27CBF-3570-4E31-99A4-22911F7EA33A'
and t1.TaxTime ='2019-01'
CREATE TABLE RP_TaxCalculationItemResult(
id INT IDENTITY(1,1), --表的其他字段(行转列时候不需要的)
testCol VARCHAR(100), --表的其他字段(行转列时候不需要的)
TaxCalculationID VARCHAR(100),
[SalaryItemCode] VARCHAR(100),
TaxItemValue VARCHAR(100)
)
INSERT INTO RP_TaxCalculationItemResult
SELECT 'A','DEA27CBF-3570-4E31-99A4-22911F7EA33A','item1010','100' UNION ALL
SELECT 'B','DEA27CBF-3570-4E31-99A4-22911F7EA33A','item1020','100' UNION ALL
SELECT 'C','DEA27CBF-3570-4E31-99A4-22911F7EA33A','item1030','100' UNION ALL
SELECT 'D','DEA27CBF-3570-4E31-99A4-22911F7EA33A','item1040','100'
--测试数据
--行转列的时候得注意一个问题,因为我这边不知道你的表结构,所以你说会出来一万多条记录也是正常的,因为在行转列的时候没有过滤掉其他字段。
--比如下面这样两个sql:
--把RP_TaxCalculationItemResult表中的数据行转列插入到表tab中(原来的sql,没过滤的)
DECLARE @name VARCHAR(max),@sql VARCHAR(max)
set @name =stuff((SELECT DISTINCT ',['+[SalaryItemCode]+']' from RP_TaxCalculationItemResult for xml PATH('')),1,1,'')
set @sql ='SELECT * from RP_TaxCalculationItemResult pivot(max(TaxItemValue)for SalaryItemCode in('+@name+'))a'
EXEC( @sql);
--把RP_TaxCalculationItemResult表中的数据行转列插入到表tab中(过滤掉其他字段)
DECLARE @name VARCHAR(max),@sql VARCHAR(max)
set @name =stuff((SELECT DISTINCT ',['+[SalaryItemCode]+']' from RP_TaxCalculationItemResult for xml PATH('')),1,1,'')
set @sql ='SELECT * from (select TaxCalculationID,[SalaryItemCode],TaxItemValue from RP_TaxCalculationItemResult) as a pivot(max(TaxItemValue)for SalaryItemCode in('+@name+'))a'
EXEC( @sql);
你会出现1万多条数据,是因为在行转列的时候没有过滤其他不相关字段,你用过滤的那个sql就行了
--模拟你的数据表
CREATE TABLE RP_TaxCalculation(
SalaryGroupID VARCHAR(100), --薪资组ID
TaxTime VARCHAR(20), --薪资月份
TaxCalculationID VARCHAR(100) --同RP_TaxCalculationItemResult关联的字段
)
INSERT INTO RP_TaxCalculation
SELECT 'DEA27CBF-3570-4E31-99A4-22911F7EA33A','2019-01','C0FB623D-2CC8-431E-9D9E-003804AACF30'
--RP_TaxCalculation和RP_TaxCalculationItemResult是一对多关系,以TaxCalculationID关联
CREATE TABLE RP_TaxCalculationItemResult(
TaxCalculationID VARCHAR(100), --同RP_TaxCalculation关联的字段
[SalaryItemCode] VARCHAR(100), ---薪资项
TaxItemValue VARCHAR(100)
)
INSERT INTO RP_TaxCalculationItemResult
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1010','100' union all
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1020','200' union all
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1030','300' union all
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1040','600' union all
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1050','700' union all
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1061','900' union all
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1070','1200' union all
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1080','130' union all
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1090','150' union all
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1100','160' union all
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1110','170' union all
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1120','800' union all
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1270','900' union all
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1140','1000' union all
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1150','1500' union all
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1160','1600' union all
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1170','1600' union all
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1180','1070' union all
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1190','1080' union all
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1200','1090' union all
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1210','1300' union all
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1220','1400' union all
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1230','1300' union all
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1240','1040' union all
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1250','1007' union all
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1260','1008' union all
SELECT 'C0FB623D-2CC8-431E-9D9E-003804AACF30','item1226','1009'
--测试数据结束
--把RP_TaxCalculationItemResult表中的数据行转列插入到表tab中(过滤掉其他字段)
DECLARE @name VARCHAR(max),@sql VARCHAR(max)
set @name =stuff((SELECT DISTINCT ',['+[SalaryItemCode]+']' from RP_TaxCalculationItemResult
WHERE SalaryItemCode IN('item1010','item1020','item1030','item1040','item1050','item1061','item1070',
'item1080','item1090','item1100','item1110','item1120','item1270','item1140','item1150','item1160',
'item1170','item1180','item1190','item1200','item1210','item1220','item1230','item1240','item1250','item1260','item1226') for xml PATH('')),1,1,'')
set @sql ='SELECT * into tab from (select TaxCalculationID,[SalaryItemCode],TaxItemValue from RP_TaxCalculationItemResult) as a pivot(max(TaxItemValue)for SalaryItemCode in('+@name+'))a'
EXEC( @sql);
SELECT *
FROM dbo.RP_TaxCalculation AS a
LEFT JOIN tab AS b ON a.TaxCalculationID=b.TaxCalculationID
WHERE SalaryGroupID='DEA27CBF-3570-4E31-99A4-22911F7EA33A'
AND TaxTime='2019-01';
是这样一种逻辑吗?你要的结果不是上面那个图那样的吗?
CREATE TABLE RP_TaxCalculationItemResult(
id INT IDENTITY(1,1), --表的其他字段(行转列时候不需要的)
testCol VARCHAR(100), --表的其他字段(行转列时候不需要的)
TaxCalculationID VARCHAR(100),
[SalaryItemCode] VARCHAR(100),
TaxItemValue VARCHAR(100)
)
INSERT INTO RP_TaxCalculationItemResult
SELECT 'A','DEA27CBF-3570-4E31-99A4-22911F7EA33A','item1010','100' UNION ALL
SELECT 'B','DEA27CBF-3570-4E31-99A4-22911F7EA33A','item1020','100' UNION ALL
SELECT 'C','DEA27CBF-3570-4E31-99A4-22911F7EA33A','item1030','100' UNION ALL
SELECT 'D','DEA27CBF-3570-4E31-99A4-22911F7EA33A','item1040','100'
--测试数据
--行转列的时候得注意一个问题,因为我这边不知道你的表结构,所以你说会出来一万多条记录也是正常的,因为在行转列的时候没有过滤掉其他字段。
--比如下面这样两个sql:
--把RP_TaxCalculationItemResult表中的数据行转列插入到表tab中(原来的sql,没过滤的)
DECLARE @name VARCHAR(max),@sql VARCHAR(max)
set @name =stuff((SELECT DISTINCT ',['+[SalaryItemCode]+']' from RP_TaxCalculationItemResult for xml PATH('')),1,1,'')
set @sql ='SELECT * from RP_TaxCalculationItemResult pivot(max(TaxItemValue)for SalaryItemCode in('+@name+'))a'
EXEC( @sql);
--把RP_TaxCalculationItemResult表中的数据行转列插入到表tab中(过滤掉其他字段)
DECLARE @name VARCHAR(max),@sql VARCHAR(max)
set @name =stuff((SELECT DISTINCT ',['+[SalaryItemCode]+']' from RP_TaxCalculationItemResult for xml PATH('')),1,1,'')
set @sql ='SELECT * from (select TaxCalculationID,[SalaryItemCode],TaxItemValue from RP_TaxCalculationItemResult) as a pivot(max(TaxItemValue)for SalaryItemCode in('+@name+'))a'
EXEC( @sql);
SELECT TOP 3 TaxCalculationID,SalaryGroupID,EmployeeID,OrgID,TaxTime
FROM dbo.RP_TaxCalculation --RP_TaxCalculation,个税计算主表,每个人,每个月会有一条记录。
WHERE SalaryGroupID='DEA27CBF-3570-4E31-99A4-22911F7EA33A' --薪资组ID
AND TaxTime='2019-01'--薪资月份
SELECT TOP 3 TaxCalculationItemResultID,TaxCalculationID,SalaryItemCode,SalaryItemName,TaxItemValue
FROM dbo.RP_TaxCalculationItemResult --RP_TaxCalculationItemResult,个税计算结果表,
--根据TaxCalculationID和RP_TaxCalculation关联,RP_TaxCalculation和RP_TaxCalculationItemResult是一对多的关系。
WHERE TaxCalculationID='C0FB623D-2CC8-431E-9D9E-003804AACF30'
AND SalaryItemCode IN('item1010','item1020','item1030','item1040','item1050','item1061','item1070',--薪资项,每个都代表一个和个税计算有关的条件
'item1080','item1090','item1100','item1110','item1120','item1270','item1140','item1150','item1160',--薪资项
'item1170','item1180','item1190','item1200','item1210','item1220','item1230','item1240','item1250','item1260','item1226')--薪资项
--把RP_TaxCalculationItemResult表中的数据行转列插入到表tab中
DECLARE @name VARCHAR(max),@sql VARCHAR(max)
set @name =stuff((SELECT DISTINCT ',['+[SalaryItemCode]+']' from RP_TaxCalculationItemResult for xml PATH('')),1,1,'')
set @sql ='SELECT * into tab from RP_TaxCalculationItemResult pivot(max(TaxItemValue)for SalaryItemCode in('+@name+'))a'
EXEC( @sql);
--再用RP_TaxCalculation表去连接tab表
SELECT
t1.TaxCalculationID,
t1.EmployeeID,
t1.TaxTime,
t1.SalaryGroupID,
t2.EmployeeCode,
t2.EmployeeName,
--状态 0:草稿, 1:已提交, 2:已审核
(CASE
WHEN taxEnsur.RecordStatus=1 THEN '草稿'
WHEN taxEnsur.RecordStatus=2 THEN '已提交'
ELSE '草稿' END) AS RecordStatusName,
a.item1010,
a.item1020,
a.item1030,
a.item1040,
a.item1050,
a.item1061,
a.item1070,
a.item1080,
a.item1090,
a.item1100,
a.item1110,
a.item1120,
a.item1270,
a.item1140,
a.item1150,
a.item1160,
a.item1170,
a.item1180,
a.item1190,
a.item1200,
a.item1210,
a.item1220,
a.item1230,
a.item1240,
a.item1250,
a.item1260,
a.item1226
FROM dbo.RP_TaxCalculation t1
LEFT JOIN dbo.RP_TaxEnsure taxEnsur
ON taxEnsur.SalaryGroupID = t1.SalaryGroupID
AND taxEnsur.TaxTime = t1.TaxTime
LEFT JOIN HR_Employee t2
ON t1.EmployeeID = t2.EmployeeID
LEFT JOIN tab AS a
ON t1.TaxCalculationID = a.TaxCalculationID
WHERE t1.SalaryGroupID = 'DEA27CBF-3570-4E31-99A4-22911F7EA33A'
AND t1.TaxTime = '2019-01'