计算转置 [英] Transpose with calculation

问题描述

尊敬的先生，

这是示例:
Employeeid流程月份扣除代码金额
100 02/22/2012 D [13] 150
100 02/22/2012 D [14] 200

我需要在单行中输入以下内容:

Employeeid processmonth LoanAdvance MandatoryPF
100 02/22/2012 150200


注意:
D [13]作为LoanAdvance

D [14]作为强制性PF


谢谢，
请帮助我

Dear sir,

Here is the example:
Employeeid processmonth deductioncode Amount
100 02/22/2012 D[13] 150
100 02/22/2012 D[14] 200

I need in a single row as below:

Employeeid processmonth LoanAdvance MandatoryPF
100 02/22/2012 150 200


Note:
D[13] as a LoanAdvance

D[14] as a MandatoryPF


Thanks,
Please help me

推荐答案

请尝试这个

与employeeCTE AS
(选择Employeeid，processmonth，
案例推导代码
当"D [13]"则以LoanAdvance的金额结束时，
案例推导码
当"D [14]"则作为强制PF结束时的金额
来自员工)

选择Employeeid，processmonth，MAX(LoanAdvance)AS，LoanAdvance，MAX(MandatoryPF)AS，MandatoryPF
来自employeeCTE
GROUP BY Employeeid，processmonth

让我知道结果.. !!!

Please try this

WITH employeeCTE AS
(SELECT Employeeid,processmonth,
CASE deductioncode
WHEN ''D[13]'' THEN Amount END AS LoanAdvance ,
CASE deductioncode
WHEN ''D[14]'' THEN Amount END AS MandatoryPF
FROM Employee)

SELECT Employeeid,processmonth,MAX(LoanAdvance) AS LoanAdvance,MAX(MandatoryPF)AS MandatoryPF
FROM employeeCTE
GROUP BY Employeeid,processmonth

Let me know the result..!!!

尝试一下

Try This

select a.Employeeid a.processmonth a.Amount "LoanAdvance" a.Amount "MandatoryPF"
from Employee a, Employee b
where
a.Employeeid=b.Employeeid
and
a.deductioncode = 'D[13]'
and
b.deductioncode = 'D[14]'

并告诉我们您得到了什么.

And tell us what u r getting.

SELECT *   FROM Employee
pivot(avg(Amount) for deductioncode in([LoanAdvance],[MandatoryPF]))as aaaa

试试这个对我来说很好.
给我答复
祝你好运

Try this it works fine for me.
give me reply
Best Luck

这篇关于计算转置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！