I have a "datadump" table that has a bunch of mixed performance-related data. Something like:
MachID TestDate MachType Value1 Value2 ...
00001 01/01/09 Server 15 48
00001 01/02/09 Server 16 99
19999 01/01/09 Switch 32 4.9880
19999 01/02/09 Switch 32 5.8109
The trick is that the "values" columns MEAN different things for different types of machines. So we have a "xRef" table that looks like:
MachType Column Description
Server Value1 Users Connected
Server Value2 % CPU _total
Switch Value1 Number of Ports
Switch Value2 packets/ms
...
I know, weird structure, but I didn't make it, and can't change it.
I'd like to somehow "inner join" these so I can query the appropriate column headers based on the type of data. Something like this for the servers:
MachID TestDate MachType Users Connected % CPU _total Total RAM
00001 01/01/09 Server 15 48 4096
00001 01/02/09 Server 16 99 4096
and this for the switches:
MachID TestDate MachType Number of Ports packets/ms Total Cumulative kb
19999 01/01/09 Switch 32 4.9880 1024547
19999 01/02/09 Switch 32 5.8109 1029450
Is there a way to do this without doing individual hard-coded queries for each type?
Note: I will only need to query one type of object at a time. Most likely, I'll only be looking at all results between particular dates for a single MachID, if that helps. This is MS SQL 2000.
Thanks!