oracle如何判断奇数偶数,Oracle查询将行放在奇数与偶数相邻

I need some help in formulating data in Oracle. I will give an example -

I have a Table Customer with Name column.

Customer

Name

Ashish

Amit

Sunny

Bob.

I want to get output in the format where names at odd number are adjacent to names at even number; output would be

Customer

Name1 Name2

Ashish Amit

Sunny Bob

and so on...

I tried following query but it doesn't give me the required output.

select name,

case Mod(rownum,2)

when 1 then name

end col1,

case Mod(rownum,2)

when 0 then name

end col2

from Customer

解决方案

This ia basically a PIVOT of the data but Oracle10g does not have the pivot function so you will have to replicate it using an aggregate and a CASE statement. If you apply the row_number() over() as well you can transform the data into the result that you want.

select

max(case when col = 1 then name end) Name1,

max(case when col = 0 then name end) Name2

from

(

select name, mod(rownum, 2) col,

row_number() over(partition by mod(rownum, 2) order by name) rn

from customer

)

group by rn

Result:

| NAME1 | NAME2 |

------------------

| Ashish | Amit |

| Sunny | Bob |