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?

Show us your current (SQL Server) query. – jarlh Jun 01 '16 at 08:10 — jarlh, Jun 01 '16 at 08:10

score 7 · Accepted Answer · answered Jun 01 '16 at 08:15

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

score 2 · Answer 2 · answered Jun 01 '16 at 10:17

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

answered Jun 01 '16 at 10:17

mohan111

8,633
4
28
55

Thank you for your answer @mohan111. SQL Compact not accept the PIVOT. – DineshDB Jun 01 '16 at 10:37
Al though this solution won't work on compact, it will work on SQL Server, so it can still help people, +1 – sagi Jun 09 '16 at 12:42

How to get values alternate for ROW_NUMBER()?

2 Answers2