Help with DECODE function and crosstab query

From: Mark Hoffman <>
Date: 7 Oct 2003 11:35:41 -0700
Message-ID: <>

I'm a newbie at Oracle..Be gentle!

I have a table that stores information (WMI data) about computers on our network. The table looks like:


Each row in the table contains WMI data for a specific WMI class and property and for a specific computer. A sample of data that shows a software package installed on a computer might look like:

ComputerID ItemID Class Property Value

1                 1            Win32_Product    Name         Visio
1                 1            Win32_Product    Vendor       Microsoft
1                 1            Win32_Product    InstallDate  100103
1                 2            Win32_Product    Name         Symnatec
1                 2            Win32_Product    Vendor       Norton
1                 2            Win32_Product    InstallDate  092503

In this case, ItemID is used to "group" the data together, since a single software package will contain multiple rows of data. (Three in this case.)

What I need to be able to do is write a query that will do something like show the software installed for a particular computer, and display something like:

Software Vendor Installed

Visio              Microsoft      100103
Norton Antivirus   Symnatec       092503

I've read up on the DECODE function but I've not been able to get my queries to work and I'm really not sure I'm even going in the right direction? Can anyone shed some light on what this query would look like?

Remember, showing installed software is just an example of what one query would look. I understand that if this is all I wanted out of WMI, then I would just build a normalized table. If we were to build a database that resembled everything WMI could hold, it would be a huge. This is a small application, but we want the flexibility of allowing the users to request any piece of WMI data without having to change the database schema. Potentially, it might be storing CPU information, hotfix info, TCP/IP stack info, etc. That's why the de-normalized table structure was chosen. Is there a better way to accomplish what we need?

Thanks in advance for any pointers or tips! Received on Tue Oct 07 2003 - 20:35:41 CEST

Original text of this message