Convert rows to columns

问题:

I have the follwoing structure:

Emp  PayDate       Amount    
1    11/23/2010    500    
1    11/25/2010    -900    
1    11/28/2010    1000    
1    11/29/2010    2000    
2    11/25/2010    2000    
3    11/28/2010    -3000    
2    11/28/2010    4000    
3    11/29/2010    -5000

I need to get the following result if emp 1 is selected (top 3 dates and their corresponding vals - if they exist - 4th row is always ignored)

PayDate1     Amount1   Paydate2     Amount2   Paydate3    Amount3
11/23/2010   500       11/25/2010   -900      11/28/2010  1000

I need to get the following result if emp 2 is selected

Paydate1    Amount1   Paydate2     Amount2   Paydate3 Amount3
11/25/2010  2000      11/28/2010   4000      NULL     NULL

I need to get the following result if emp 3 is selected

Paydate1       Amount1   Paydate2      Amount2   Paydate3   Amount3
11/28/2010    -3000      11/29/2010    -5000

To get the respective data in rows I can run the following query:

select top 3 Paydate, Amount from Table where Emp = @Emp

But how do I get result in a pivoted fashion?

回答1:

CREATE TABLE dbo.Table1 
(
    Emp        int,
    PayDate    datetime,
    Amount     int
)
GO

INSERT INTO dbo.Table1 VALUES (1, '11/23/2010',500)
INSERT INTO dbo.Table1 VALUES (1, '11/25/2010',-900)
INSERT INTO dbo.Table1 VALUES (1, '11/28/2010',1000)
INSERT INTO dbo.Table1 VALUES (1, '11/29/2010',2000)
INSERT INTO dbo.Table1 VALUES (2, '11/25/2010',2000)
INSERT INTO dbo.Table1 VALUES (3, '11/28/2010',-3000)
INSERT INTO dbo.Table1 VALUES (2, '11/28/2010',4000)
INSERT INTO dbo.Table1 VALUES (3, '11/29/2010',-5000)


;WITH cte AS
(SELECT Emp, PayDate, Amount, PayDateRowNumber
FROM 
(SELECT Emp,
       PayDate,
       Amount,
       ROW_NUMBER() OVER (PARTITION BY Emp ORDER BY PayDate) AS PayDateRowNumber
FROM Table1) AS RankedTable1
WHERE PayDateRowNumber < 4)
SELECT c1.Emp AS Emp, c1.PayDate AS PayDate1
        ,c1.Amount AS Amount1, c2.PayDate AS PayDate2
        ,c2.Amount AS Amount2, c3.PayDate AS PayDate3, c3.Amount AS Amount3
FROM cte c1
LEFT JOIN cte c2 ON c2.Emp = c1.Emp AND c2.PayDateRowNumber = 2
LEFT JOIN cte c3 ON c3.Emp = c2.Emp AND c3.PayDateRowNumber = 3
WHERE c1.PayDateRowNumber = 1

Output is:

Some caveats are that it won't aggregate amounts for the same employer/date (though can easily be changed). Also may want to change to review use of ROW_NUMBER() versus RANK() and DENSE_RANK() depending on your definition of "TOP 3"

回答2:

There's an excellent article on Pivots with SQL Server 2005+ here.