ORCL - Pivot

Sometime users request converting multiple rows to columns for same ID, like the following:

with l as

 (select '333' id, 'email1' email, 1 xsequence  from dual

  union  select '333' id, 'email2' email, 2 xsequence  from dual

  union  select '333' id, 'email3' email, 3 xsequence   from dual)

select * from l;

333 email1 1

333 email2 2

333 email3 3

Sometimes users are OK with comma concatenated like this (with listagg function):

333 email1,email2,email3 (one column)

And most time users want like this: 

333 email1 email2 email3 (3 columns)

So in order to get that, I used to do an old way: first rank() them and doing case then group max, like the following:

select n.id, max(email1) e1, max(email2) e2, max(email3) e3

  from (select m.id,  case  when rn = 1 then m.email  end email1,

               case when rn = 2 then m.email end email2,

               case when rn = 3 then m.email end email3

          from m) n  group by id

By using Pivot, it will cut the CASE and MAX, just like this:

select *  from m pivot (max(email) for rn in(1, 2, 3))

with l as

 (select '333' id, 'email1' email, 1 xsequence

    from dual

  union

  select '333' id, 'email2' email, 2 xsequence

    from dual

  union

  select '333' id, 'email3' email, 3 xsequence

    from dual),

m as

 (select distinct l.id, l.email,

                  rank() over(partition by l.id order by l.xsequence) rn  

    from l)

select *  from m pivot (max(email) for rn in(1, 2, 3))

If you want to select them for other joined query, you could set the column alias to get rid of the number(rn), since you can not pick/select the column started with numbers.

select *   from m

pivot (max(email) for rn in(1 as e1, 2 as e2, 3 as e3))

I made a video to show the process, thanks for watching!

You can online search for Pivot documentation and examples.

"Oracle 11g introduced the new PIVOT clause that allows you to write cross-tabulation queries which transpose rows into columns, aggregating data in the process of the transposing. As a result, the output of a pivot operation returns more columns and fewer rows than the starting data set.

In this syntax, following the PIVOT keyword are three clauses:

• pivot_clause specifies the column(s) that you want to aggregate. The pivot_clause performs an implicitly GROUP BY based on all columns which are not specified in the clause, along with values provided by the pivot_in_clause.
• pivot_for_clause specifies the column that you want to group or pivot.
• pivot_in_clause defines a filter for column(s) in the pivot_for_clause. The aggregation for each value in the pivot_in_clause will be rotated into a separate column."