• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5997
  • Last Modified:

Transpose rows to columns as a subquery (SQL)

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.
Steve Endow
Steve Endow
  • 3
  • 2
  • 2
  • +1
2 Solutions
Is this SQL 2000 or 2005?
I'm hoping you have SQL 2005, cause if so, it will be MUCH easier.  Here is a SQL 2005 query if you do in fact have SQL 2005.  It's not very pretty, but it works.  It uses a PIVOT table to give you the results you are looking for.

SELECT EmployeeID, CheckNmbr, CheckDate, ISNULL([1],0) AS Loan1Pmt, ISNULL([2],0) AS Loan2Pmt, ISNULL([3],0) AS Loan3Pmt, ISNULL([4],0) AS Loan4Pmt, ISNULL([5],0) AS Loan5Pmt
       SELECT t1.EmployeeID, t1.CheckNmbr, t1.CheckDate, t2.Amount, ROW_NUMBER() OVER(PARTITION BY t2.EmployeeID, t2.CheckNmbr, t2.CheckDate ORDER BY t2.EmployeeID, t2.CheckNmbr) AS DeductionNum
              SELECT EmployeeID, CheckNmbr, CheckDate
                FROM CheckDetail
                WHERE PayCode = 'SALARY'
                GROUP BY EmployeeID, CheckNmbr, CheckDate
              ) t1
           LEFT OUTER JOIN CheckDetail t2 ON t1.EmployeeID = t2.EmployeeID AND t1.CheckNmbr = t2.CheckNmbr AND t1.CheckDate = t2.CheckDate
         AND t2.PayCode <> 'SALARY'
       ) t3
  PIVOT (SUM(Amount) FOR DeductionNum IN ([1], [2], [3], [4], [5])) AS PivotTable
Here is another example that uses a temp table and as far as I know will work with sql 2000 but you might want to test it I don't have 2000 installed right now, not a really pretty query but I hope it sparks some ideas.
CREATE TABLE #EmpCheckCom(Inc int, EmpId varchar(50), CheckNum int, CheckDate datetime, PayCode varchar(50), Amount decimal(7,2))
DECLARE @EmpCheckInc Table(Inc int IDENTITY, EmpId varchar(50), CheckNum int, CheckDate datetime, PayCode varchar(50), Amount decimal(7,2))
DECLARE @EmpId varchar(50)
DECLARE @inc int
INSERT INTO @EmpCheckInc (EmpId, CheckNum, CheckDate, PayCode, Amount)(SELECT EmpId,CheckNum,CheckDate,PayCode,Amount FROM EmpCheckInfo)
SELECT EmpId FROM EmpCheckInfo
OPEN EmpCheck
	SELECT @inc = MIN(Inc)-1 FROM @EmpCheckInc WHERE EmpId = @EmpId
	INSERT INTO #EmpCheckCom(Inc,EmpId,CheckNum,CheckDate,PayCode,Amount) (SELECT Inc-@inc,EmpId,CheckNum,CheckDate,PayCode,Amount FROM @EmpCheckInc WHERE EmpId = @EmpId)
CLOSE EmpCheck
SELECT e.EmpId, ISNULL(q1.Amount,0), ISNULL(q2.Amount,0), ISNULL(q3.Amount,0), ISNULL(q4.Amount,0), ISNULL(q5.Amount,0)  
FROM #EmpCheckCom e
LEFT JOIN(SELECT EmpId,Amount FROM #EmpCheckCom WHERE Inc = 2)q1 ON q1.EmpId = e.EmpId
LEFT JOIN(SELECT EmpId,Amount FROM #EmpCheckCom WHERE Inc = 3)q2 ON q2.EmpId = e.EmpId
LEFT JOIN(SELECT EmpId,Amount FROM #EmpCheckCom WHERE Inc = 4)q3 ON q3.EmpId = e.EmpId
LEFT JOIN(SELECT EmpId,Amount FROM #EmpCheckCom WHERE Inc = 5)q4 ON q4.EmpId = e.EmpId
LEFT JOIN(SELECT EmpId,Amount FROM #EmpCheckCom WHERE Inc = 6)q5 ON q5.EmpId = e.EmpId
JOIN(SELECT MAX(Inc) AS Inc,EmpId FROM #EmpCheckCom GROUP BY EmpId)q6 ON q6.Inc = e.Inc AND q6.EmpId = e.EmpId

Open in new window

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

In hind site I could adjust it so that it wouldn't be bound to only 5 columns it would work out for as many different PayCodes as you had.
Looks like a report to me. Most reporting tools can do this MUCH more simply than is possible in SQL. SQL isn't ideal as a report-writing language.
Steve EndowMicrosoft MVP - Dynamics GPAuthor Commented:
Thanks aaron and brady for the thorough responses.

It turns out the company uses SQL 2005, which sounds like good news.  I didn't know which version they had, and didn't realize the version would make a difference.

I'm going to test both of the proposed solutions now and see if I can push them into my larger query.
Steve EndowMicrosoft MVP - Dynamics GPAuthor Commented:
aaron, I just tested your query, and it looks like it works great.  I left outer joined it into my larger query as a derived table, and was easily able to pull the loan amount fields into my query.

brady, thanks for your thorough response as well--I was assuming that a temp table was my backup plan if an inline query couldn't do the trick.

Steve EndowMicrosoft MVP - Dynamics GPAuthor Commented:
Excellent solutions guys, and very impressive work.  I really appreciate it, as it saved me from spending hours coming up with a cheezy solution that would have also hurt my budget.  Points to both of you for the thorough responses.

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now