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."

Windows 10 Your Phone

Just found Windows 10’s Your Phone app on my $300 ThinkPad Yoga book, which I really like. The App links your android phone with PC. Of course it works with iPhone but it's said it works best for Android users, letting you text from your PC, sync your notifications, and wirelessly transfer photos back and forth, also screen your phone to TV.

OK, Sounds pretty good, usually I USB connect my Huawei Pro20 with PC to transfer files, specially pictures and songs. So with Phone app we can do those wirelessly, WIRELESSLY, yes, nowadays it's a very popular thing.

Setup Your Phone is extremely easy, just click Windows Logo on your PC and scroll down the bottom, click it and follow along, I made a silly video, you can watch if you like to support my starting YouTube Channel. 

Also a quick tip about the Windows OneDrive, OneDrive is Microsoft's cloud storage for consumers, and it is built into Windows 10. For free, it comes with 15 GB of storage, and there are a couple of paid tiers to increase that storage. 15GB is plenty for normal people:), but make sure you are aware of the OneDrive sync options.

The OneDrive cloud icon in the Windows taskbar notification area, right click it will show some options/tasks you can do.  I also added a little talk about this on the below clip.

Well, let's leave our phones somewhere and focus on the PC:))