Oracle查询将行放在奇数与偶数相邻 [英] Oracle query to put rows at odd number adjacent to even number

问题描述

在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 

以此类推...

我尝试了以下查询，但未提供所需的输出.

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

推荐答案

这基本上是数据的PIVOT，但是Oracle10g没有数据透视功能，因此您将不得不使用汇总和CASE复制它陈述.如果您也应用 row_number() over() 您可以将数据转换为所需的结果.

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

请参见带演示的SQL提琴

结果:

|  NAME1 | NAME2 |
------------------
| Ashish |  Amit |
|  Sunny |   Bob |

这篇关于Oracle查询将行放在奇数与偶数相邻的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！