Transpose with calculation

Question

3.00/5 (1 vote)

See more:

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

Posted 21-Feb-12 22:21pm

stanly jeba singh

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CGN007 · Answer 1 · 2012-02-22T00:56:00

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..!!!

CRDave1988 · Answer 2 · 2012-02-21T22:33:00

Solution 1

Try This

SQL

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.

Posted 21-Feb-12 22:33pm

CRDave1988

Comments

stanly jeba singh 22-Feb-12 4:45am

i do not get any result for that query, but get the header's Like Employeeid,Processmonth,LoanAdvance,MandatoryPf

Nilesh Patil Kolhapur · Answer 3 · 2012-02-21T23:00:00

Solution 2

Hi,

SQL

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

Posted 21-Feb-12 23:00pm

Nilesh Patil Kolhapur

Updated 21-Feb-12 23:02pm

v2

Comments

CGN007 22-Feb-12 5:33am

The above query results LoanAdvance & MandatoryPF filled with Nulls

Nilesh Patil Kolhapur 22-Feb-12 6:13am

impossible

CGN007 22-Feb-12 6:16am

Fill the table
Employeeid processmonth deductioncode Amount
100 02/22/2012 D[13] 150
100 02/22/2012 D[14] 200

And try...!!!

Nilesh Patil Kolhapur 22-Feb-12 6:23am

wait

Nilesh Patil Kolhapur 22-Feb-12 6:36am

SELECT * FROM Employee
pivot(avg(Amount) for deductioncode in(D13,D14))as aaaa
try this and remove [] from your deductioncode values

CGN007 22-Feb-12 6:44am

Desired result should be like this format
Employeeid processmonth LoanAdvance MandatoryPF
100 02/22/2012 150 200

Also editing the input is not a proper way,I think..!!!

Nilesh Patil Kolhapur 22-Feb-12 6:59am

but boss using this not possible

CGN007 22-Feb-12 6:59am

Please try my answer.
Hope this will solve the problem...!!!

Nilesh Patil Kolhapur 22-Feb-12 7:06am

yes i try but null value diplayed well try boss

CGN007 22-Feb-12 7:13am

I think you already removed the [] sign...

Please fill the table with following and try
Employeeid processmonth deductioncode Amount
100 02/22/2012 D[13] 150
100 02/22/2012 D[14] 200

Nilesh Patil Kolhapur 22-Feb-12 7:40am

yes great yaar really nice work my 5

CGN007 22-Feb-12 7:58am

Thanks...!!!

Nilesh Patil Kolhapur · Answer 4 · 2012-02-22T00:57:00

Hi,
Finally Done
try this

SQL

SELECT Employeeid,processmonth,D13 as LoanAdvance, D14 as MandatoryPF
from (SELECT Employeeid, processmonth, deductioncode,Amount
FROM temp1) p
Pivot(avg (Amount) for deductioncode in(D13,D14))as aaaa

I learned lot from this Question.
Thanks.

*Note- Dont use [] ok

Good Luck