sql - Oracle pivot operator

Question

I am new to oracle pivot. Is this possible?

I have two columns Type and 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

Thanks

Answer 1

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

Result:

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

Result:

A  B
-- --
a1 b1