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

You can do the following:

SELECT Col1, Col2,
    MAX(CASE Col3 WHEN 'R1' THEN 'R1' END) as Res1,
    MAX(CASE Col3 WHEN 'R2' THEN 'R2' END) as Res2,
    MAX(CASE Col3 WHEN 'R3' THEN 'R3' END) as Res3
FROM MyTable
GROUP BY Col1, Col2

Answer 2

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

Answer 3

What you seek is a dynamic crosstab query that will build columns out of the values in Col3. The SQL language was not designed for dynamic column generation and thus this type of query cannot be done in T-SQL without some fugly dynamic SQL. Instead, I would recommend you build your query in a middle-tier component or use a reporting tool.