Oracle PL/SQL: Pivoting a SQL Query within SQL

This blog post includes a rather simple script that will allow a developer to quickly pivot a single-table query so that rather than returning a single row will the data it returns multiple rows representing Column Name/Value combinations.


Let's assume we have a fairly simple table called PIVOTTEST this table, apart from displaying a distinct lack of imagination as far as naming goes, contains four columns ID (number), NAME (varchar2), DATE_CREATED (date) and DATE_LAST_UPDATED (date). It's created using the SQL:

create table PIVOTTEST
(
  ID                number,
  NAME              varchar2(60),
  DATE_CREATED      date,
  DATE_LAST_UPDATED date
);

In order to do our test let's put a few records into the table;

insert into pivottest(id, name, date_created, date_last_updated) values (1, 'ANDY', sysdate-200, sysdate - 5);
insert into pivottest(id, name, date_created, date_last_updated) values (2, 'BRETT', sysdate-190, sysdate - 4);
insert into pivottest(id, name, date_created, date_last_updated) values (3, 'COLIN', sysdate-180, sysdate - 3);
insert into pivottest(id, name, date_created, date_last_updated) values (4, 'IAN', sysdate-170, sysdate - 2);
insert into pivottest(id, name, date_created, date_last_updated) values (5, 'ADAM', sysdate-160, sysdate - 1);
commit;

If we do a SELECT * FROM PIVOTTEST WHERE ID = 1 we get a single record back:

 ID NAME DATE_CREATED DATE_LAST_UPDATED
 1 ANDY 10-MAY-2008 21-NOV-2008 

Yours dates will be different, and the format will be determined by your system settings but you get the point.

Assuming we'd prefer to have the results as multiple columns we would be aiming at something looking like:

 Column Name Column Value 
 ID 1 
 NAME ANDY 
 DATE_CREATED 10-MAY-2008 
 DATE_LAST_UPDATED 21-NOV-2008 

The easiest way to do this is to use multiple UNIONS and select each field we're interested in in turn:

select 1 ID, 'ID' Name, to_char(ID) Value from APPS.PIVOTTEST where ID = 1
union
select 2 ID, 'NAME' Name, NAME Value from APPS.PIVOTTEST where ID = 1
union
select 3 ID, 'DATE_CREATED' Name,to_char(DATE_CREATED) Value from APPS.PIVOTTEST where ID = 1
union
select 4 ID, 'DATE_LAST_UPDATED' Name, to_char(DATE_LAST_UPDATED) Value from APPS.PIVOTTEST where ID = 1

First thing; in order to allow the UNION to work the columns have to be of the same type. I've go for "character" just because it's the one practically every type has in common. In theory it will depend on the data you're working with but in practice you'll almost certainly want to use characters!

You'll notice that I've included the "WHERE ID =1" clause at the end to just give me the record I'm interested in and I've numbered the select statements so that when they are all joined together with the UNION the columns still come out in the order I'm expecting (if you remove the 1, 2, 3, 4 from the SELECT ... ID then the records come back in alphabetical column name order ... do you want that?).

Because the table details are held in Oracle you can actually do the same thing using a script:

-- Created on 25-Nov-2008 by APE06 
declare
  -- Local variables here
  cursor c_Columns is
    select atc.COLUMN_ID,
           atc.owner,
           atc.table_name,
           atc.COLUMN_NAME,
           atc.DATA_TYPE
      from all_tab_columns atc
     where atc.owner = 'APPS'
       and atc.TABLE_NAME = upper('PIVOTTEST')
     order by atc.COLUMN_ID;


  v_Where varchar2(255) := 'ID = 1';
begin
  for v_Column in c_Columns loop
    dbms_output.put_line('select ' || v_Column.column_id ||
                         ' ID, ''' || v_Column.column_name ||
                         ''' Name, nvl(' || 
                         case 
                         when v_Column.Data_Type = 'DATE' then 'to_char(' || v_Column.column_name || ',''DD-MON-YYYY'')' 
                         when v_Column.Data_Type = 'NUMBER' then 'to_char(' || v_Column.column_name || ')' 
                         else v_Column.column_name 
                         end ||
                         ', '''') Value from ' || v_Column.Owner || '.' ||
                         v_Column.Table_name || ' where ' || v_Where);
    dbms_output.put_line('union');
  end loop;
end;

You need to change the OWNER (from APPS), the TABLE_NAME (from PIVOTTEST), and your where clause condition to return a single row (from ID = 1) and then you're ready to go.

You should also watch out for the "union" that gets tacked on the end ... you'll need to delete that (I could have added a "select '','' from dual where 3=1" to get rid of it but ... well you can do that yourselves now can't you? (I'm also using PL/SQL Developer a a test window which makes copy/pasting very easy so I don't really need 100% accuracy).

This script will generate the SQL to query the table as a Column Name/ Value combination - it also does a few other "nice" things like specifying the format for the date and displaying when the column has a null value.

I hope this helps!

Building an MSI to Deploy Fonts using wItem Installer (formerly Installer2Go)

This blog post gives you detailed instructions on how to build a simple MSI installer that will deploy fonts to Windows users. It is envisaged that this will be used in conjunction with Active Directory (Group Policy) to control the deployment.

Why Use an MSI?

The first thing to understand is that there are many ways of doing the deployment of fonts in Windows. Each machine you are seeking to deploy to has a "Fonts" directory under it's Windows installation folder. In a default Windows installation (XP, Vista, 7, or even 2003 Server) this installation folder is called C:\WINDOWS and the directory used for Fonts is C:\WINDOWS\Fonts.

It would be possible to write a script to copy the files into this directory, you could even run that script from a GPO, but using an MSI is much neater, creates a nice error trail if things go wrong (and they will in any large environment), and is much more controllable using Active Directory and Group Policy Objects (GPOs) for deployment. Unfortunately every company will have a few machines where the installation hasbeen done into a different directory (C:\WINNT for instance!), maybe even a couple where the installation is actually on the D: drive. Putting scripts in place to test all of this is a pain. Not just the writing of the script; it's the testing and proving that it works that consumes all the time.

In short; use an MSI. It really will save you time in the long run. And if you're going to use an MSI why not use one that's free? Hence my recommendation for wItem Installer (which was formerly called Installer2Go).

Getting The Software
Of course the obvious prerequisite is going to be obtaining the MSI-building software. Fairly simple; just visit this URL;

http://www.witemsoft.com/togo/ (wItem Software)

The software is also available from other sources such as CNET if that link doesn't work.

Make sure you read the software licensing information when prompted by the installation!

The software is provided by wItem Software as "Freeware".

Using The Software
Once you've got the software installed start it up;

Installer2Go Version 4.2 (Freeware Version)

For size reasons I've colour-compressed the images, but hopefully they will still be good enough to give you some idea of the process.

As you can see I'm using version 4.2.5 of the software, as the MSI standard has changed very little (especially when all you need to do is install fonts!) I would expect future versions to be pretty much the same.

Step 1: Click on the "New" icon at the top left. You'll now see a whole heap of tabs;

New Project Multi-Tabbed Dialog

You should now fill in all the fields on the General Info tab. You don't have to, but it's tidy and in IT we like tidy. Just show anyone in IT your windows desktop with it's gazillion icons and watch them flinch ... As the installer is going to be for internal use only you don't have to spend quite as much time on this bit ... Oh. You didn't. You've already scrolled past this bit to;

Step 2: Click on the "Files" tab and expand the "Windows" folder;

"File" Tab

Select "Fonts" and then drag-and-drop the fonts you wish to install into this Window (they will usually have the extension .TTF). Once you've done that you're ready for the next step.

Step 3: Click the "Create Setup" (right-most) tab enter an Output Folder and a Filename and make sure that "Create Self-Extracting Executable that will contain your MSI file" is not checked.

Next click "Build" and your MSI will magically appear in your installation directory.

NOTE: At the end of an attended installation a dialog will be displayed promoting SDS Software. When you're doing an unattended installation no dialog is displayed and if you're deploying via Group Policy then it's the unattended installation that you'll be interested in!