DineshDB DineshDB - 4 months ago 5x
SQL Question

How to get values alternate for ROW_NUMBER()?

I have a table values like below,

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,

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

I got the result using
SQL Server
. But I want the same result in
SQL Compact
, But I can't able to use
in SQL Compact.

I'm using SQL Compact version - 4.0.8482.1

How can I got the result. Help me to get the answer.

Thanks in advice


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,
FROM YourTable t
GROUP BY t.order