Question on pivot tables

Question

Question on pivot tables

Ryan D.

SSCommitted

Points: 1595
More actions
February 21, 2009 at 3:02 pm

#126650

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')

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 1

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 2

[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]

JohnSmith001 Right there with Babe Points: 765 More actions · Answer 3

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

Ryan D. SSCommitted Points: 1595 More actions · Answer 4

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

muratistanbul SSC Veteran Points: 267 More actions · Answer 5

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

LutzM SSC Guru Points: 107049 More actions · Answer 6

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 .

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 7

muratistanbul (3/14/2010)
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

Are you using SQL Server 2005 or better? If not, you'll need to use a cross tab instead of PIVOT.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

johnabraham1980 Grasshopper Points: 15 More actions · Answer 8

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/[/url] where you can find example.

hope this will work for you

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 9

johnabraham1980 (8/24/2013)
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/[/url] 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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)