DineshDB - 7 months ago 13

SQL Question

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()`

`SQL Server`

`SQL Compact`

`ROW_NUMBER()`

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
```