So can anyone help with this query? I'm trying to pull all my ESX hosts out of our UIM DB which can be done with this query:
--This will pull all the cs_id's of all the ESX Hosts in UIM:
select cs_id from CM_COMPUTER_SYSTEM_ATTR where cs_attr_key ='PrimaryRole' and cs_attr_value = 'VirtualMachineHost'
and then take all those CS_ID's and then pull the following fields out of the CM_COMPUTER_SYSTEM_ATTR table and show the them as columns. Trying to pull all the ESX Hosts and their version of ESX they are running.
The CM_COMPUTER_SYSTEM_ATTR has multiple rows with the same cs_id which then have the property and the value in that row.
Need to know how do I transpose or pivot the rows into columns from this table.
select * from CM_COMPUTER_SYSTEM_ATTR
where cs_attr_key ='PrimaryRole' and cs_attr_value = 'VirtualMachineHost' or cs_attr_key = 'HypervisorVersion' or cs_attr_key = 'PrimaryOSVersion' or cs_attr_key = 'PrimaryDnsName' or cs_attr_key = 'VirtualizationEnvironment'
order by cs_id,cs_attr_key