Re: Help with DECODE function and crosstab query

From: Jan <janik_at_pobox.sk>
Date: 8 Oct 2003 01:16:23 -0700
Message-ID: <81511301.0310080016.66fa4805_at_posting.google.com>

didn`t test it, but you are looking for something like this:

SELECT computer_id,

       item_id,
       MAX(Name) Name,
       MAX(Vendor) Vendor,
       Max(Installed) Installed

  FROM (
SELECT computer_id,

       item_id,
       DECODE(Property,'Name',Value) Name,
       DECODE(Property,'Vendor',Value) Vendor,
       DECODE(Property,'InstallDate',Value) Installed

  FROM your_table)
 GROUP BY computer_id, item_id

powergeeks_at_hotmail.com (Mark Hoffman) wrote in message news:<a0c105e1.0310071035.68e22362_at_posting.google.com>...
> 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:
>
> ComputerID
> ItemID
> Class
> Property
> Value
>
> 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
> Antivirus
> 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 Wed Oct 08 2003 - 10:16:23 CEST