Oracle 10g – Pivoting data

Oracle 10g doesn’t have the Pivot function. To convert a common type a data (Date, Id, Value) into a list of (Date, Value of Id1, Value of Id2, …), if you know the number of columns (and their names), you can a decode function (basically a SQL IF or CASE) function to do it.

Demonstration below with the code to build the table and test it.

CREATE TABLE "TESTTABLE"("DATETIME" DATE,
"SCENARIOI" NUMBER,
"MYVALUE" NUMBER);
 REM INSERTING into TESTTABLE
Insert into TESTTABLE (DATETIME,SCENARIOI,MYVALUE) values (to_date('01.10.12 00:00','DD.MM.RR HH24:MI'),1,10);
Insert into TESTTABLE (DATETIME,SCENARIOI,MYVALUE) values (to_date('01.10.12 00:00','DD.MM.RR HH24:MI'),2,20);
Insert into TESTTABLE (DATETIME,SCENARIOI,MYVALUE) values (to_date('01.10.12 01:00','DD.MM.RR HH24:MI'),1,15);
Insert into TESTTABLE (DATETIME,SCENARIOI,MYVALUE) values (to_date('01.10.12 01:00','DD.MM.RR HH24:MI'),2,20);

The created and populated table: 

To extract the data in a matrix format, with one column per scenario, we can use either of the queries below:

select datetime,
 max(decode(scenarioi,1, MyValue)),
 max(decode(scenarioi,2, MyValue)) from testtable
group by datetime
order by 1;
SELECT DateTime, MAX(One) as Sc1, MAX(Two) as Sc2
 FROM (SELECT datetime,
 decode(scenarioi, 1, MyValue) as One,
 decode(scenarioi, 2, MyValue) as Two
 FROM testtable
 )
GROUP BY DateTime
ORDER BY 1;

An example output of the query: 

It would be nice to have a query that could automatically select a number of columns and convert them, it seems that requires some PLSQL, but sadly I’ve not been able to figure it out yet. If anyone has such an example, just leave a comment.

Apple apps – Remote

Remote is one of these apps that apple has put up on their AppStore without really putting it forwards, it’s not even on their Apps by Apple page. But it is an amazing addition to your iPhone.

You can control your iTunes library from the comfort of your phone. Away from the computer? No problem, you can still switch tracks, pause or change the volume.

A wonderful little app, free.

Migrating my mum to OS X – Adressbook and applescript

One potentially show-stopping problem for migrating my mum was that she had over 1500 contacts in Windows Live Mail (WLM). These were sorted into many groups, which had to be recreated in the mac’s address book application. The main problem was that Microsoft’s tool does not give any option to export the groups a contact is associated with, either through hotmail, through the app or any other method I tried until I found this solution:

First, you should export the complete contact list from Windows Live Mail, this can be done from the edge of the address book tool bar. I used .vcf format, which creates 1 card per contact. You should also be able to find this option in File -> Export.

Exporting contacts from Windows Live Mail

On the Windows PC, in the address book for WLM, you have the split screen view of the groups on the right and the people assigned to them on the left. If you double click on a group name, that group opens in a detail window. In the bottom part of that window is a selectable list of all the contacts belonging to that group. Copy the whole text area with CTRL-C and paste that in Excel with CTRL-V. Then you’ll need to split that input into separate lines since that are comma separated. Save each group as an individual text file (usually named the same as the group) with the people belonging to group.

The next step is to move these files to the mac. First of all import all the contacts into the mac’s address book. This can be done by dragging all the contacts .VCF cards to the address book window.

Now comes the reassigning the contacts to the groups. What I did was create the group in the address book, then edit my following applescript to set the group I wanted to import to “MyDesiredGroupName, then run the following script:

tell me to set the text item delimiters to (ASCII character 13)
tell application “Finder”
  set Names to paragraphs of (read (choose file with prompt “Pick text file containing names to go in group”))
  repeat with nextLine in Names
    if length of nextLine is greater than 0 then
      tell application “Address Book”
        set thePeople to (every person whose name = nextLine)
        set theGroup toMyDesiredGroupName
        repeat with thisPerson in thePeople
          add thisPerson to group theGroup
        end repeat
        save application “Address Book”
      end tell
    end if
  end repeat
end tell

By setting the MyDesiredGroupName, you should be able to get most of your contacts imported into the correct group. I had a slight issue with about 1-2% of the contacts not being selected or found properly, especially those that had accents in their names (é, è, ö, ü, …). If you look in the output log of the script, it is fairly easy to find those that were not selected correctly by the line “set thePeople to (every person whose name = nextLine)” since they appear with a { } empty selection in the output log. 

In fact, I took the output log and passed it into a text editor searching for { } to find the few people that were not correctly imported. That allowed me to make sure the groups were correctly populated.

Remember to check the number of people belonging to the group at the end of the import.

That’s it. I hope this helps someone else make the switch from WLM to apple’s address book.

iTunes U – Interesting courses

I just downloaded the iTunes U app for my iPhone and the first course I got into was the iPhone and iPad application development from Stanford. I find it an excellent primer for an initial foray into the world of iOS development and also an overview of the possibilities of the Xcode development environment.

Checkout the lecture on Xcode and source code management to see how well Xcode and Git are integrated. It makes my visual studio seem like tools from a distant past.

Has anyone got other suggestions for good courses to follow?

Awesomenote and Evernote

A fantastic feature of Awesomenote is that it synchronizes with both Evernote and Google docs. This means your notes follow you to every platform and device you can imagine or access the net with.

In Evernote, your notes appear without any of the Awesomenote backgrounds, but with a short amount of metadata at the bottom of the note.

Once sync is setup, it is one button press away. Ensuring you are always backed up and accessible.

Migrating my mum to OS X – Experiences

So I spent the whole week-end migrating my mum from the old Windows XP to the new iMac.

First of all, the iMac is gorgeous. Take it out of the box, put it on the desktop, plug in the power cable and you are done. Amazing! If there was no legacy to migrate, it would of been a matter of minutes to be done, but… Now comes the hard part, migrating: The 50’000+ Emails, 1’500+ contacts sorted into groups, 60+ GB of Music, 30+ GB of data files.

The software we installed is:

  • Pages and Microsoft Office 2011 for Mac – For the office stuff, Pages was an addition to see if it could be used for more design oriented work (instead of Quark or InDesign). Both Purchased.
  • Roxio Toast 11 – The old burning program that support .nrg (Nero) file images. I tried to convert the images to ISO, but since they are binary images of Audio cds, none of the conversion tools (nrg2iso, nrg4iso, etc..) worked. Purchased
  • Inkscape – For the existing cd centers, previously in Corel draw (.cdr), the files were converted on the PC both to SVG and to PDF so that they could be stored and later edited on the mac. Free.
  • Audacity – For audio processing of aquired music. Free.
  • VLC – Video player supporting every format under the sun. Free.
  • Transmission – Torrent software. Free.
  • Gimp and Paintbrush – Both image editing software. Free.
  • Blue Griffon – WYSIWYG HTML editing. Free (with no addons).
  • FileZilla – FTP client for updating the websites. Free.
  • Avery DesignPro® for Mac® – For making new CD labels, it’s an amazing piece of software. Just works. Free.

For the browser, we simply used Safari.

For the mail, currently we are using the Apple Mail app, but it was not very successful so far. Maybe it will change. I hope to post more about the mail in a further blog. Both the import of the emails and the import of the address book posed substantial challenges.

For the music, we are using iTunes to manage the collection, although we disabled the “Auto sort the music collection” option of iTunes so that the music was not moved around into new folders. The music collection being extensive and mostly untagged makes iTune’s management a nightmare.

Installing the printers (and accessing the multifunction scanners on top of these) was easy  as pie. By just plugging in the printers, they were detected, an apple update was downloaded and they were ready to be used. Both the HP and the Brother worked. We used Image Capture to do scanning.