How to get values alternate for ROW_NUMBER()?

Question

I have a table with values like these:

Name    Order    Innings
Suresh    1         1
Ramesh    2         1
Sekar     3         1
Raju      1         2
Vinoth    2         2
Ramu      3         2

I want the result be like this:

1stInn  2ndInn  Order
Suresh  Raju      1
Ramesh  Vinoth    2
Sekar   Ramu      3

I got the result using ROW_NUMBER() in SQL Server.

I want the same result in SQL Compact, But I can't use ROW_NUMBER() in SQL Compact.

I'm using SQL Compact version - 4.0.8482.1

How can I get the result?

Answer 1

Why do you need ROW_NUMBER()? you can use conditional aggregation using CASE EXPRESSION :

SELECT MAX(CASE WHEN t.innings = 1 THEN t.name END) as 1stInn,
       MAX(CASE WHEN t.innings = 2 THEN t.name END) as 2sndInn,
       t.Order
FROM YourTable t
GROUP BY t.order

Answer 2

simple Pivot will give the similar result

DECLARE @Table1  TABLE 
    ( Name  varchar(6),  [Order]  int,  Innings  int)
;

INSERT INTO @Table1
    ( Name ,  [Order] ,  Innings )
VALUES
    ('Suresh', 1, 1),
    ('Ramesh', 2, 1),
    ('Sekar', 3, 1),
    ('Raju', 1, 2),
    ('Vinoth', 2, 2),
    ('Ramu', 3, 2)
;
select [1] AS '1stinn',[2] AS '2ndinn',[order] from(
select Name ,  [Order] ,  Innings from  @Table1)T
PIVOT (MAX(NAME) FOR Innings IN ([1],[2]))PVT