troubleshooting Question

Transpose rows to columns as a subquery (SQL)

Avatar of Steve Endow
Steve EndowFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005SQL
8 Comments2 Solutions6668 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
aaronakin

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros