?
Solved

Transpose rows to columns as a subquery (SQL)

Posted on 2008-10-01
8
Medium Priority
?
5,818 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:Steve Endow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 11

Expert Comment

by:aaronakin
ID: 22617633
Is this SQL 2000 or 2005?
0
 
LVL 11

Accepted Solution

by:
aaronakin earned 1200 total points
ID: 22617762
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
  FROM
       (
       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
         FROM
              (
              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
0
 
LVL 5

Assisted Solution

by:brady1408
brady1408 earned 800 total points
ID: 22618042
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)
 
DECLARE EmpCheck CURSOR FOR
SELECT EmpId FROM EmpCheckInfo
GROUP BY EmpId
 
OPEN EmpCheck
 
FETCH NEXT FROM EmpCheck INTO @EmpId
 
WHILE @@FETCH_STATUS = 0
BEGIN
	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)
	FETCH NEXT FROM EmpCheck INTO @EmpId
END
 
CLOSE EmpCheck
DEALLOCATE 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
DROP TABLE #EmpCheckCom

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:brady1408
ID: 22618083
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.
0
 
LVL 22

Expert Comment

by:dportas
ID: 22618479
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.
0
 
LVL 18

Author Comment

by:Steve Endow
ID: 22619458
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.
0
 
LVL 18

Author Comment

by:Steve Endow
ID: 22619594
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.

0
 
LVL 18

Author Closing Comment

by:Steve Endow
ID: 31502047
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.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question