Pivoting the first two records... tricky

[ookete]

I am trying to find the most efficient way to pivot the first two values for every "group by" in a recordset. For example, here is my table:

Account Form    Date     Score
1       A       1/8/07   89%
1       B       2/7/07   95%
1       A       8/3/07   72%
1       A      12/1/07   87%
2       C       3/1/07   98%
2       C     10/16/07   95%

I need to group by Account and Form, and pivot the first two results together in the same row, as follows:

Account Form    Date1    Score1  Date2    Score2
1       A       1/8/07   89%     8/3/07   72%
1       B       2/7/07   95%
2       C       3/1/07   98%   10/16/07   95%

Notice that Account 1, Form B only has one record, therefore the second columns are null. Also notice that the third Form A record does not even come in. I had thoughts of using a "Top 2" combined with FIRST and LAST to pivot, but I ran into problems. Any thoughts? Thanks!

 

[lameid]

Check out an Access FAQ...
faq701-4524

The only difference I think you will see is the delimeters for literals in the SQL.

 

[PWise]

i think that this will work

SELECT     Account, Form, Date1, Score, date2, score2
FROM         (SELECT     Account, Form, MIN(Date) Date1, Score Score1
                       FROM          Tablename
                       GROUP BY Account, Form, Score) Firstdate LEFT OUTER JOIN
                          (SELECT     Account, Form, MIN(Date) Date2, Score Score2
                            FROM          Tablename
                            GROUP BY Account, Form, Score) ScondDate ON Firstdate.Account = ScondDate.Account AND Firstdate.Form = ScondDate.Form AND 
                      ScondDate.[date] > Firstdate.date1

 

[ookete]

lameid, I didn't think that the TRANSFORM command were available in SQL Server? (Then again, I haven't spent much time in 2005 yet). Is that new?

 

[lameid]

My mistake... I thought it was part of ANSI-92 SQL and therefore SQL server would support it. I am probably wrong on both counts (I lent my SQL book to someone).

Although, pwise's solution looks good, as usual.

 

[lameid]

On the other hand, see "Using PIVOT and UNPIVOT" in BOL.