Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select to row to colum

Author  Topic 

nonsec
Starting Member

26 Posts

Posted - 2013-10-02 : 15:30:39
Hi everyone;
I have a column name called countername it has different values like;
% Free Space
Free Megabytes
Avg. Disk sec/Transfer

What i am trying to do place them next to each other in the query;
Here is my query;


SELECT
dbo.vManagedEntity.Path,
dbo.vPerformanceRuleInstance.InstanceName,
Perf.vPerfHourly.AverageValue,
Perf.vPerfHourly.DateTime,
dbo.vManagedEntity.DisplayName,
dbo.vPerformanceRule.ObjectName,
dbo.vPerformanceRule.CounterName,
dbo.vManagedEntity.Name

FROM
Perf.vPerfHourly INNER JOIN dbo.vPerformanceRuleInstance ON Perf.vPerfHourly.PerformanceRuleInstanceRowId = dbo.vPerformanceRuleInstance.PerformanceRuleInstanceRowId INNER JOIN dbo.vManagedEntity ON Perf.vPerfHourly.ManagedEntityRowId = dbo.vManagedEntity.ManagedEntityRowId INNER JOIN dbo.vPerformanceRule ON dbo.vPerformanceRuleInstance.RuleRowId = dbo.vPerformanceRule.RuleRowId
WHERE (dbo.vPerformanceRule.ObjectName = 'Logicaldisk') and CounterName like '%Free%'


Here is the output;

Path InstanceName AverageValue DateTime DisplayName ObjectName CounterName Name
MYServerName.abc.com L: 94.51095581 00:00.0 Cluster Disk 4 L-Drive Logs LogicalDisk % Free Space Cluster Disk 4 L-Drive Logs
MYServerName.abc.com L: 94.51095581 00:00.0 Cluster Disk 4 L-Drive Logs LogicalDisk % Free Space Cluster Disk 4 L-Drive Logs
MYServerName.abc.com L: 157648 00:00.0 Cluster Disk 4 L-Drive Logs LogicalDisk Free Megabytes Cluster Disk 4 L-Drive Logs
MYServerName.abc.com L: 157648 00:00.0 Cluster Disk 4 L-Drive Logs LogicalDisk Free Megabytes Cluster Disk 4 L-Drive Logs


What i need is something like this;

Path InstanceName Free Megabytes % Free Space
MYServerName.abc.com L: 5000 75


where Free megabytes and % Free Space is next to each other.

any input greatly appreciated.
Thank you.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-02 : 15:45:43
Something like this. However, the aggregate function may be sum or average (or something else) depending on the data you are working with. For cases where the SUM function is to be used, the calculation would be correct. But for average this would not give you the correct results unless all drives happened to have the same total space. So you will need to calculate the total space and available free space for each drive and then take the ratio of the sums to find the average free space.
SELECT
[Path],
[InstanceName],
SUM(CASE WHEN CounterName = 'Free Megabytes' THEN AverageValue END) AS [Free Megabytes],
AVG(CASE WHEN CounterName = '% Free Space' THEN AverageValue END) AS [%Free Space]
FROM
(
SELECT dbo.vManagedEntity.Path ,
dbo.vPerformanceRuleInstance.InstanceName ,
Perf.vPerfHourly.AverageValue ,
Perf.vPerfHourly.DateTime ,
dbo.vManagedEntity.DisplayName ,
dbo.vPerformanceRule.ObjectName ,
dbo.vPerformanceRule.CounterName ,
dbo.vManagedEntity.Name
FROM Perf.vPerfHourly
INNER JOIN dbo.vPerformanceRuleInstance ON Perf.vPerfHourly.PerformanceRuleInstanceRowId = dbo.vPerformanceRuleInstance.PerformanceRuleInstanceRowId
INNER JOIN dbo.vManagedEntity ON Perf.vPerfHourly.ManagedEntityRowId = dbo.vManagedEntity.ManagedEntityRowId
INNER JOIN dbo.vPerformanceRule ON dbo.vPerformanceRuleInstance.RuleRowId = dbo.vPerformanceRule.RuleRowId
WHERE ( dbo.vPerformanceRule.ObjectName = 'Logicaldisk' )
AND CounterName LIKE '%Free%'
)s
GROUP BY
[Path],
[InstanceName]
Go to Top of Page

nonsec
Starting Member

26 Posts

Posted - 2013-10-02 : 15:59:55
Thank you James. I appreciate the query but i don`t want to use any aggregation just need one sample of the exiting value. The results are pretty much duplicate of every hours. Is there a way that i can select once distinct value for each InstanceName in that query without doing any aggregation?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-02 : 16:13:40
If you change the aggregate function to MAX (or MIN) that should give you one of the values. It may not pick the values corresponding to the same time stamp if you do it that way, but if the values are duplicates, that shouldn't matter.
Go to Top of Page

nonsec
Starting Member

26 Posts

Posted - 2013-10-03 : 14:41:37
Thank you very much.
Go to Top of Page
   

- Advertisement -