Sql Pivot top N rows only

Question

I have Following structure

Col1 Col2 Col3  
---------------
F     P    R1
F     P    R2
F     P    R3
F     P    R4

Col3 values can be any thing Now I want in following format only top 3

Col1 Col2 Res1 Res2 Res3  
------------------------------
F     P    R1   R2   R3

Answer 1

If using SQL Server 2005+, Oracle 8i+, PostgreSQL 8.4+--you can use analytic functions:

  SELECT x.col1, x.col2,
         MAX(CASE WHEN x.rk = 1 THEN x.col3 END) AS Res1,
         MAX(CASE WHEN x.rk = 2 THEN x.col3 END) AS Res2,
         MAX(CASE WHEN x.rk = 3 THEN x.col3 END) AS Res3
    FROM (SELECT yt.col1,
                 yt.col2,
                 yt.col3,
                 ROW_NUMBER() OVER(PARTITION BY yt.col1, yt.col2
                                       ORDER BY yt.col3) AS rk
            FROM YOUR_TABLE yt) x
GROUP BY x.col1, x.col2