Moving records to single line

Question

I am trying to shift SQL records to single line so can easily use it in my other queries where I am joining tables.John example worked with me..

My table looks something like this.

EmpID | DeductionType | HoursDeduction | DeductionAmount | Year | Month
ABC   | F             |  4             | $2.00           | 2013 | 10
ABC   | H             |  2             | $1.50           | 2013 | 10

I my case I have to select two fields on the basis of another field.

SELECT empid, 
       year1    AS [Year], 
       month1   AS [Month], 
       Max(CASE deductiontype 
             WHEN 'F' THEN hoursdeduction 
             ELSE 0 
           END) FullDeductionHours, 
       Max(CASE deductiontype 
             WHEN 'F' THEN deductionamount 
             ELSE 0 
           END) FullDeductionAmount, 
       Max(CASE deductiontype 
             WHEN 'H' THEN hoursdeduction 
             ELSE 0 
           END) HalfDeductionHours, 
       Max(CASE deductiontype 
             WHEN 'H' THEN deductionamount 
             ELSE 0 
           END) HalfHourDeductionAmount 
FROM   mydeductiontable 
GROUP  BY empid, 
          year1, 
      month1

I only want to know will it be efficient and not slow my stored procedure where I am trying to find deduction hours and using it to calculations for Payroll data. Is there anyway we can make query more smart?

You could try 'windowed functions': `ROW_NUMBER() over (partition by EmpID, DeductionType order by HoursDeduction desc)seqHours ,ROW_NUMBER() over (partition by EmpID, DeductionType order by DeductionAmount desc)seqAmount` — Kiril Rusev, Dec 17 '13 at 09:37
Kiril I am not getting hours and amount in seqHours and seqAmount, instead I am getting sequence number like 1 and 2. Could you explain how can I use it. — Almas Mahfooz, Dec 18 '13 at 12:31
The query you have is OK only if you have 2 deduction types (F and H). However, since it is hard coded (when 'F' and when 'H') you wont be able to retrieve any results if a new deduction type is added. In other words, if you these are the only 2 types you have, your query is fine. Otherwise, using seqHOURS and seqAMOUNT `where= 1` will display the max Value regardless of the `deduction type`. Also, I will do my best to post the solution with row_numbers. — Kiril Rusev, Dec 18 '13 at 13:30

Moving records to single line

0 Answers0