DineshDB DineshDB - 6 months ago 7
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
ROW_NUMBER()
in
SQL Server
. But I want the same result in
SQL Compact
, But I can't able to use
ROW_NUMBER()
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

Answer

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