how to get the even and odd column separately with separate column by query

Question

I have an input:

id

I want get even and odd columns separately by columns in specified output like this

id col

here id and col are separate columns id contains the odd number and col contains the even number for specified input

If whatever you are working in supports modulus, that will be your answer for sorting. if result mod 2 > 0 then it's a key, else it's a value — Chris Caviness, Sep 10 '14 at 19:25
Are we assuming your table never has gaps in numbers? What if the next 2 numbers were 11, 13 with no 12? — SQLChao, Sep 10 '14 at 19:40

Barmar · Answer 1 · 2014-09-10T19:34:30.123

9

SELECT MIN(id) as id, MAX(id) as col
FROM YourTable
GROUP BY FLOOR((id+1)/2)

For IDs 1 and 2, (id+1)/2 are 2/2 = 1 and 3/2 = 1.5, respectively, and FLOOR then returns 1 for both of them. Similarly, for 3 and 4, this is 2, and so on. So it groups all the input rows into pairs based on this formula. Then it uses MIN and MAX within each group to get the lower and higher IDs of the pairs.

edited Sep 10 '14 at 19:34

answered Sep 10 '14 at 19:27

Barmar

741,623
53
500
612

2

what is your logic can you explain? – Shanthi Ponna Sep 10 '14 at 19:31
I've added some details. I thought it would be obvious if you know simple arithmetic. – Barmar Sep 10 '14 at 19:35

score 2 · Answer 2 · answered Sep 10 '14 at 19:45

2

Joined the table on itself

select * 
from yourTable tA
left join yourTable tb on tA.id = (tB.id - 1)
where tA.id % 2 <> 0

answered Sep 10 '14 at 19:45

SQLChao

7,709
1
17
32

score 0 · Answer 3 · edited May 23 '17 at 12:05

0

To show odd:

Select * from MEN where (RowID % 2) = 1

To show even:

Select * from MEN where (RowID % 2) = 0

Now, just join those two result sets and that's it.

Source

edited May 23 '17 at 12:05

Community

1
1

answered Sep 10 '14 at 19:27

Hoh

1,196
1
12
30

i want that in single query – Shanthi Ponna Sep 10 '14 at 19:30
How do you merge these to get the combine results? You need to join them, what is the join condition? (This is a rhetorical question -- the answer is basically the same as the `GROUP BY` condition in my answer.) – Barmar Sep 10 '14 at 19:36
@Barmar you're completely right, I posted this answer before I saw yours. I can say that after a lot of years of experience with SQL (not a lot like yours, but still a lot) I could possible never come with such a easy solution like you did. Thanks for teaching me. – Hoh Sep 10 '14 at 19:39

score 0 · Answer 4 · answered Sep 10 '14 at 19:36

0

If you use SQL you can try:

SELECT CASE WHEN column  % 2 = 1 
            THEN column 
            ELSE null 
       END AS odds,
       CASE WHEN column  % 2 = 2 
            THEN column 
            ELSE null 
       END AS even
FROM yourtable

but not exactl as you ask

answered Sep 10 '14 at 19:36

Roberto Saldaña Contreras

1
1

This will put them in different columns, but not in the same rows. – Barmar Sep 10 '14 at 19:37
same i tried it before but i didn't get the exact answer – Shanthi Ponna Sep 10 '14 at 19:42

how to get the even and odd column separately with separate column by query

4 Answers4