Oracle枢轴运算符 [英] Oracle pivot operator

问题描述

我是oracle枢纽的新手.这可能吗?

I am new to oracle pivot. Is this possible?

我有两列Type和Value

type     value
---------------
a        a1
b        b1
c        c1
etc

我可以在一行中得到这样的东西吗?

Will I be able to get something like this, all in a single row??

a   b    c 
a1  b1   c1

尝试这样的查询时，我得到这样的输出

upon trying a query like this i am getting an output like this

  select A,B from tbl
  pivot (max(value) for type in ('a' as A,'b' as B))

  ------------------------------------
    A    B
   null  b1
   a1    null

谢谢

推荐答案

之所以得到这样的输出，仅仅是因为您针对一个表(您的tbl表)发出了select语句，该表可能包含一列(主键列)唯一地标识一行，而pivot运算符将考虑该列的值.这是一个简单的示例:

You are getting the output like that simply because you are issuing select statement against a table (your tbl table) which presumably contains a column(primary key column for instance) which uniquely identifies a row and pivot operator takes into consideration values of that column. Here is a simple example:

/*assume it's your table tbl */
with tbl(unique_col, col1, col2) as(
  select 1, 'a',  'a1' from dual union all
  select 2, 'b',  'b1' from dual union all
  select 3, 'c',  'c1' from dual
)

针对此类表的查询将为您提供您在问题中提供的输出(不必要的输出):

A query against such a table will give you that output(undesirable output) you provided in the question:

select A,B 
  from tbl
pivot(
  max(col2) for col1 in ('a' as A,'b' as B)
)

结果:

A    B
--   --
a1   null   
null b1

为了产生所需的输出，您需要排除一行具有唯一值的列:

In order to produce desired output, you need to exclude the column with unique value for a row:

select A
     , B 
  from (select col1 
             , col2  /*selecting only those columns we are interested in*/
           from tbl ) 
  pivot(
    max(col2) for col1 in ('a' as A,'b' as B)
  )

结果:

A  B
-- --
a1 b1 

这篇关于Oracle枢轴运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！