February 21, 2009 at 3:02 pm
I have been collecting performance data through Typeperf.
I'm trying to create a pivot table for report purposes.
I have 9 column items. I can generate the pivot table however, the query returns 9 rows. with null values. Is there any way to flatten out the query to return one row per server.
Thanks
select * from dbo.getresults
PIVOT
(AVG([AVG]) for in
([Buffer Manager\Buffer cache hit ratio] ,[PhysicalDisk(_Total)\% Disk Time] ,[PhysicalDisk(_Total)\Current Disk Queue Length] ,[Processor(_Total)\% Processor Time] ,[Memory\Page Faults/sec] ,[Memory Manager\Total Server Memory (KB)] ,[general statistics\user connections] ,[Transactions/sec]
)
) AS PVT
where servername in ('MYSERVER')
February 21, 2009 at 3:06 pm
Please see the following article... for performance reasons, you may want to reconsider and use simple cross-tabs, instead.
http://www.sqlservercentral.com/articles/T-SQL/63681/
Also, if you'd like a tested answer to your good question, please see the article at the link in my signature below.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 22, 2009 at 2:24 pm
[font="Verdana"]As a suggestion, have you tried loading up the data into Excel, and using the Cross Tab in Excel? That may be an easier way of achieving what you want.[/font]
February 23, 2009 at 9:25 pm
Construct your pivot query as following
select *
from
( select *
from dbo.getresults
) Main
PIVOT
(AVG([AVG]) for in
([Buffer Manager\Buffer cache hit ratio] ,[PhysicalDisk(_Total)\% Disk Time] ,[PhysicalDisk(_Total)\Current Disk Queue Length] ,[Processor(_Total)\% Processor Time] ,[Memory\Page Faults/sec] ,[Memory Manager\Total Server Memory (KB)] ,[general statistics\user connections] ,[Transactions/sec]
)
)AS PVT
John Smith
February 24, 2009 at 7:24 am
All thanks for all your suggestions, I ended using the following. I wasn't aware of a cross tab in Excel. I guess I'll have to check that one out. This was actually a lot easier than the pivot tables.
More typing but easier
select servername,
MAX(CASE WHEN = 'Buffer Manager\Buffer cache hit ratio' THEN MIN ELSE 0 END) AS [Buffer - Min]
...
from dbo.getresults
where servername in
(
'MYServerName',
...
)
group by servername
order by servername
March 14, 2010 at 5:37 pm
Hi
thanks for your informations but ALL TIMES I GET
Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 'PIVOT'.
Message Dou you have any suggestion for me
thanks
March 14, 2010 at 5:55 pm
Hi
thanks for your informations but ALL TIMES I GET
Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 'PIVOT'.
Message Dou you have any suggestion for me
Duplicate post.
Please continue discussion here .
March 14, 2010 at 9:06 pm
muratistanbul (3/14/2010)
Hithanks for your informations but ALL TIMES I GET
Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 'PIVOT'.
Message Dou you have any suggestion for me
thanks
Are you using SQL Server 2005 or better? If not, you'll need to use a cross tab instead of PIVOT.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 24, 2013 at 9:14 pm
use below syntax for pivot table in sql
SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
.............................
FROM
(<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column], .............................)
) AS <alias for the pivot table>
<optional ORDER BY clause>;
I think this will help you.
for more details, please see this link http://blogfornet.com/2013/08/using-pivot-in-sql-server/%5B/url%5D where you can find example.
hope this will work for you
August 25, 2013 at 10:25 am
johnabraham1980 (8/24/2013)
use below syntax for pivot table in sqlSELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
.............................
FROM
(<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column], .............................)
) AS <alias for the pivot table>
<optional ORDER BY clause>;
I think this will help you.
for more details, please see this link http://blogfornet.com/2013/08/using-pivot-in-sql-server/%5B/url%5D where you can find example.
hope this will work for you
Nice Link an welcome aboard.
I don't know what it is, though... I can see the need for it but it still bugs me when a blog uses moderation. Certainly not in this case but I've responded to some blogs that had a mistake or misconception in it and whomever was doing the moderating never posted the corrections but continued to post the "raves". It's bugged me ever since.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy