我在承包商付费字段中使用unpivot时遇到问题。
我发现错误“contractorspayed列的类型与unpivot列表中指定的其他列的类型冲突。”
我正在努力实现。

我试过使用cast，但仍然出现同样的错误。 CAST(ROW_NUMBER() over(partition by pr.CompanyID, pr.OfficeID, pr.SBUID, pr.TempID, pr.TransactionDate, pr.JobNumber, vwCon.ConsultantID_New order by pr.OfficeID, pr.CustomerNumber, pr.SBUID,pr.TempID, pr.TransactionDate, pr.JobNumber, vwCon.ConsultantID_New) AS numeric) rn, ```
with cte as (
SELECT vwOff.AccountSegment as [OfficeID],
vwSBU.AccountSegment as [SBUID],
pr.[TransactionDate],
pr.[CustomerNumber] as [Customer ID],
vwCon.ConsultantID_New as [Consultant_ID],
pr.[TotalFee] as [Bill Amount],
pr.[Cost] as [Pay Amount],
pr.[OnCostAmount] as [Burden Amount],
CASE when pr.[PayTypeID] = 'REG' then HoursBilled else 0 end as [Regular Hours],
CASE when pr.[PayTypeID] = 'OT' then HoursBilled else 0 end as [OT Hours],
pr.[SplitRate],
row_number() over(partition by pr.CompanyID, pr.OfficeID, pr.SBUID, pr.TempID, pr.TransactionDate, pr.JobNumber, vwCon.ConsultantID_New order by pr.OfficeID, pr.CustomerNumber, pr.SBUID,pr.TempID, pr.TransactionDate, pr.JobNumber, vwCon.ConsultantID_New) rn,

        pr.[PayTypeID],
        pr.[BatchID],
        pr.[TempID],
        pr.[TransactionType]

FROM [P_Rept] pr
INNER JOIN [vw_Global_ConsultantID] vwCon ON pr.ConsultantID = vwCon.[ConsultantID]
INNER JOIN [vw_Global_Office] vwOff ON pr.OfficeID = vwOff.[OfficeID]
INNER JOIN [vw_Global_SBU] vwSBU ON pr.SBUID = vwSBU.SBUID

WHERE pr.[TransactionDate] BETWEEN '2020-01-01' AND '2020-01-31'  

),
cte2 as (
select
[OfficeID],
[SBUID],
[TransactionDate],
[Customer ID],
[Consultant_ID],
sum([Bill Amount]) as [Bill Amount],
sum([Pay Amount]) as [Pay Amount],
sum([Burden Amount]) as [Burden Amount],
sum([Regular Hours] + [OT Hours]) as [Hours Billed],
sum([Regular Hours]) as [Regular Hours],
sum([OT Hours]) as [OT Hours],
--[SplitRate],
case when rn = 1 and ABS(SplitRate) > 15 then 1 else 0 end ContractorsPaid

from cte
group by 
         [OfficeID],
         [SBUID], 
         [TransactionDate],
         [Customer ID], 
         [Consultant_ID],  
         [SplitRate],
         case when rn = 1 and ABS(SplitRate) > 15 then 1 else 0 end,
         rn

)
select [OfficeID], [SBUID], [Customer ID], [Consultant_ID], [TransactionDate], [Account], [Value]
from cte2
unpivot([Value] for [Account] in ([Bill Amount], [Pay Amount], [Burden Amount], [Hours Billed], [Regular Hours], [OT Hours], [ContractorsPaid])) u