How to change values ​​for ROW_NUMBER ()?

Question

I have a table with values ​​similar to 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 to 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 to get the same result in SQL Compact, but I cannot use ROW_NUMBER() in SQL Compact.

I am 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 with 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

a simple pivot will give a 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