Rotating information from Recordsets to columns.

[Tim]

Hello,

This problem perplexes me and I hope that someone has done something
efficient.

Take for example the data in the MASTER..SYSPERFINFO:
SELECT
CAST(RTRIM(INSTANCE_NAME) AS VARCHAR(15)),
CAST(RTRIM(COUNTER_NAME) AS VARCHAR(31)),
CAST(RTRIM(CNTR_VALUE) AS VARCHAR(10))
FROM MASTER..SYSPERFINFO
WHERE INSTANCE_NAME = N'TEMPDB'

tempdb Data File(s) Size (KB) 51200
tempdb Log File(s) Size (KB) 1272
tempdb Log File(s) Used Size (KB) 738
tempdb Percent Log Used 58
tempdb Active Transactions 0
tempdb Transactions/sec 186281
tempdb Repl. Pending Xacts 0
tempdb Repl. Trans. Rate 0
tempdb Log Cache Reads/sec 0
tempdb Log Cache Hit Ratio 0
tempdb Log Cache Hit Ratio Base 0
tempdb Bulk Copy Rows/sec 0
tempdb Bulk Copy Throughput/sec 0
tempdb Backup/Restore Throughput/sec 0
tempdb DBCC Logical Scan Bytes/sec 0
tempdb Shrink Data Movement Bytes/sec 0
tempdb Log Flushes/sec 1578
tempdb Log Bytes Flushed/sec 67882496
tempdb Log Flush Waits/sec 226
tempdb Log Flush Wait Time 47
tempdb Log Truncations 248
tempdb Log Growths 3
tempdb Log Shrinks 0

<I did the CAST and LTRIM so that it looks better when displayed in a
browser>

I would like to keep statistics in a table with the following columns:
INSTANCE_NAME,
DATA_FILE_SIZE,
LOG_FILE_FIZE,
ACTIVE_TRANS,
TRANS_PER_SEC
So, instead of having a table with three columns and 23 rows(only 4 of
which I want), I would have a single row with 4 columns(plus the
Instance_Name).

Visualy, I want to call this a 90 degree rotation. Here's what the
select statement would then look like:
SELECT *
FROM SYSPERFINFO_ARCHIVE
WHERE INSTANCE_NAME = N'TEMPDB'

Here's the result set:
tempdb 51200 1272 0 185198

Is it possible to 'rotate' a recordset into columns?
How would it be done?

Gracias.

[Erland Sommarskog]

Tim (go**************@cfapostle.com) writes:

So, instead of having a table with three columns and 23 rows(only 4 of
which I want), I would have a single row with 4 columns(plus the
Instance_Name).

Visualy, I want to call this a 90 degree rotation. Here's what the
select statement would then look like:
SELECT *
FROM SYSPERFINFO_ARCHIVE
WHERE INSTANCE_NAME = N'TEMPDB'

Here's the result set:
tempdb 51200 1272 0 185198

select a.instance_name,
data_file_size = a.cntr_value,
log_file_size = b.cntr_value,
active_trans = c.cntr_value,
trans_per_sec = d.cntr_value
from master..sysperfinfo a
join master..sysperfinfo b on a.instance_name = b.instance_name
join master..sysperfinfo c on a.instance_name = c.instance_name
join master..sysperfinfo d on a.instance_name = d.instance_name
where a.instance_name = N'tempdb'
and a.counter_name = N'Data File(s) Size (KB)'
and b.counter_name = N'Log File(s) Size (KB)'
and c.counter_name = N'Active Transactions'
and d.counter_name = N'Transactions/sec'
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[DMAC]

Erland Sommarskog <so****@algonet.se> wrote in message

select a.instance_name,
data_file_size = a.cntr_value,
log_file_size = b.cntr_value,
active_trans = c.cntr_value,
trans_per_sec = d.cntr_value
from master..sysperfinfo a
join master..sysperfinfo b on a.instance_name = b.instance_name
join master..sysperfinfo c on a.instance_name = c.instance_name
join master..sysperfinfo d on a.instance_name = d.instance_name
where a.instance_name = N'tempdb'
and a.counter_name = N'Data File(s) Size (KB)'
and b.counter_name = N'Log File(s) Size (KB)'
and c.counter_name = N'Active Transactions'
and d.counter_name = N'Transactions/sec'

Wow!. There are times I feel like a complete newbie. Hats off to the
set based thinkers as opposed to us poor old procedural bods.