Solved

Transpose rows to columns as a subquery (SQL)

Posted on 2008-10-01
8
5,506 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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 11

Expert Comment

by:aaronakin
Comment Utility
Is this SQL 2000 or 2005?
0
 
LVL 11

Accepted Solution

by:
aaronakin earned 300 total points
Comment Utility
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 200 total points
Comment Utility
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
 
LVL 5

Expert Comment

by:brady1408
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 22

Expert Comment

by:dportas
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now