SQL Help to turn one data from Two Columns into a Rows

Question

I am trying to create a view for Windows Performance Monitors. The issue I have is that Table I have called "AllPerf" has 2 of the columns called "PerfCounter" and "Application" have several different names of Performance counters and Applications in them.

Ideally, what I want for my columns is: Time, Computer, Application name, and then the names of all the PerfCounter rows into columns.

I created a view of only the row names I want, and with the current view I created below, I get this output:

-----------------------------------------------------------------------
| mem.AlertTime       | Computer | PerfCounter | Application | Value  |
-----------------------------------------------------------------------
| 2019-03-15 14:49:02 | WEB-04   | Vrt_Bytes   | System      |0.1368  |
| 2019-03-15 14:49:02 | WEB-05   | Vrt_Bytes   | System      |2440    |
| 2019-03-15 14:49:02 | WEB-06   | Handles     | w3wp        |1508    |
| 2019-03-15 14:49:02 | WEB-04   | Page_Faults | System      |0.00419 |
| 2019-03-15 14:49:02 | WEB-04   | Prvt_Bytes  | System      |0.1368  |
-----------------------------------------------------------------------

I've tried the solutions in this link below, but when I can successfully list row names as columns, I get no Values populated under the columns. Efficiently convert rows to columns in sql server

And since I don't have much experience with SQL in general, I can't seem to extrapolate the simple examples with my more complex data

This is what i'm using for my SELECT statement for my current view.

SELECT  mem.AlertTime, 
    Computer, 
        CASE WHEN mem.PerfCounter = 'Virtual Bytes' THEN 'Virt_Bytes' 
    WHEN mem.PerfCounter = 'Private Bytes' THEN 'Prvt_Bytes' 
    WHEN mem.PerfCounter = 'Page Faults/sec' THEN 'Page_Faults_Sec' 
    WHEN mem.PerfCounter = 'Thread Count' THEN 'Threads' 
    WHEN mem.PerfCounter LIKE '%Handle%' THEN 'Handles' 
    END AS PerfCounter, 
    PerfInstance AS Application, 
    Value
FROM    dbo.AllPerf AS mem

And what I want is something like this:

--------------------------------------------------------------------------------------------
| mem.AlertTime       | Computer |Application| Vrt_Bytes| Prvt_Bytes| Handles| Page_Faults |
-------------------------------------------------------------------------------------------
| 2019-03-15 14:49:02 | WEB-04   | System    | 12440     | 24.13    |   13   |   0.14      |
| 2019-03-15 14:49:02 | WEB-04   | w3wp      | 7396      | 4.2309   |   13   |    0        |
| 2019-03-15 14:49:02 | WEB-05   | w3wp      | 1538      | 0.1368   |  1538  |    0        |
| 2019-03-15 14:49:02 | WEB-05   | System    | 6629      | 6500     |  1835  |    5        |
| 2019-03-15 14:49:02 | WEB-06   | System    | 2440      | 0.1368   |   13   |    0        |
--------------------------------------------------------------------------------------------

And If I had my pie-in-the-sky wish, I would covert the MBytes under Mem_Bytes to GB, but I couldn't successfully make CASE statements and include the math in the result

score 0 · Answer 1 · answered Apr 02 '19 at 16:44

I created a variable table @MYTAB in which I used to test the script below

You can replace @MYTAB by the whole view you used in your post

Here is the code, let me know if it is Ok for you

DECLARE @MYTAB AS table(AlertTime datetime,Computer varchar(50),PerfCounter varchar(50),Application varchar(50),Value decimal(10,5))

insert into @mytab 
values('2019-03-15 14:49:02','WEB-04'   , 'Vrt_Bytes', 'System' ,'0.1368') ,
('2019-03-15 14:49:02' , 'WEB-05'   , 'Vrt_Bytes'   , 'System'      ,2440    ),
('2019-03-15 14:49:02' , 'WEB-06'   , 'Handles'     , 'w3wp'        ,1508    ),
('2019-03-15 14:49:02' , 'WEB-04'   , 'Page_Faults' , 'System'      ,0.00419 ),
('2019-03-15 14:49:02' , 'WEB-04'   , 'Prvt_Bytes'  , 'System'      ,0.1368  )


select alerttime,computer,application,piv.*
 from @MYTAB d
 pivot

 ( max(value)  for perfcounter in ([vrt_bytes],[handles],[page_faults]) ) piv

thanks @Kemal --- this is very helpful and I'm very close, however: When I run the Select and Pivot you posted above against my current view, I get 2 sets of columns for AlertTime, Computer, Application, then AlertTime, Computer, Application, and all the pivot columns: ie: AlertTime | Computer | Application | AlertTime | Computer | Application | Vrt_Bytes | Prvt_Bytes | .... how do I prevent that? — Ferdinand, Apr 02 '19 at 22:05

SQL Help to turn one data from Two Columns into a Rows

1 Answers1