Background: I have developed a SQL query that retrieves 401k info from employee payroll check transactions. One requirement or the query is to output 5 fields in the query that represent up to 5 different 401k loan deductions that MAY be deducted from the paycheck. Some checks have no loan deductions, some checks may have 1 or more deductions.
Request: I am looking for a subquery that will allow me to transpose an *arbitrary* number of rows into 5 columns. Ideally I would like to be able to use the subquery for these 5 fields within my larger query; however, if that is not possible and I have to use temp tables (or some other solution), please let me know and offer your recommendation for the best alternative approach.
Source Data (Table = CheckDetail):
EmployeeID CheckNmbr CheckDate PayCode Amount
SMITH01 1001 10/03/2008 SALARY 1000.00
SMITH01 1001 10/03/2008 LN101 25.00
JONES01 1005 10/03/2008 SALARY 1130.00
JONES01 1005 10/03/2008 LN205A 50.00
JONES01 1005 10/03/2008 LN215B 19.00
JONES01 1005 10/03/2008 LN312 12.00
JONES01 1005 10/03/2008 LN80 10.00
JONES01 1005 10/03/2008 LN16X 14.00
BAKER01 1012 10/03/2008 SALARY 1350.00
Relevant records have a Pay Code beginning with "LN" = Loan, but every loan code is different.
Desired Output:
EmployeeID CheckDate Loan1Pmt Loan2Pmt Loan3Pmt Loan4Pmt Loan5Pmt
SMITH01 10/03/2008 25.00 0.00 0.00 0.00 0.00
JONES01 10/03/2008 50.00 19.00 12.00 10.00 14.00
BAKER01 10/03/2008 0.00 0.00 0.00 0.00 0.00
The "order" of the loan payment amounts is NOT currently important or part of the requirement. So the records in the CheckDetail do not have to correspond to any one of the 5 Loan Payment fields.
Start Free Trial
