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.
Author |
Topic |
mitin
Yak Posting Veteran
81 Posts |
Posted - 2013-03-07 : 08:39:31
|
I have data in a table as in the below sample:PMHost PMInstance PMObject Timestamp PM_Counter PMValueahermontest1 C LogicalDisk 22728812772 Disk Write Bytes/Sec 3816ahermontest1 D LogicalDisk 22728812773 Disk Write Bytes/Sec 1483ahermontest1 E LogicalDisk 22728812774 % Free Space 74ahermontest1 C LogicalDisk 22728812775 Free Megabytes 37911ahermontest1 D LogicalDisk 22728812776 Split IO/Sec 0 and I require it to be displayed as follows:PMHost PMInstance PMObject Timestamp Disk Write Bytes/sec % Free Space Free Megabytes Split IO/Secahermontest1 C LogicalDisk 22728812772 3816 ahermontest1 D LogicalDisk 22728812773 1483 ahermontest1 E LogicalDisk 22728812774 74 ahermontest1 C LogicalDisk 22728812775 37911 ahermontest1 D LogicalDisk 22728812776 0 Please note that there are just under 100 different 'PM_Counters' in the table, and there are many more 'PMValues'.So as you can see I need each PM_counter to be turned into a column name and the corresponding PM_vaue to be inserted into that column for each row. I know this will create large table in terms of the number of columns, but I then plan on separating out into separate tales from there...How can I achieve this? many thanks guys! |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-07 : 09:10:38
|
use pivot------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
mitin
Yak Posting Veteran
81 Posts |
Posted - 2013-03-07 : 09:50:30
|
How do you get the data into the format I require with pivot, I don't know how it would be used here, it seems rather bamboozling when you are new to SQL... |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-07 : 10:02:08
|
Here is an example you can copy and paste to an SSMS window and run. For your actual problem, you wouldn't need to create the #tmp table. I am doing that here just to demonstrate:CREATE TABLE #tmp (PMHost VARCHAR(32), PMInstance CHAR(1), PMObject varchar(32),Timestamp bigint, PM_Counter VARCHAR(32), PMValue INT);INSERT INTO #tmp VALUES('ahermontest1','C','LogicalDisk','22728812772','Disk Write Bytes/Sec',3816),('ahermontest1','D','LogicalDisk','22728812773','Disk Write Bytes/Sec', 1483),('ahermontest1','E','LogicalDisk','22728812774','% Free Space',74),('ahermontest1','C','LogicalDisk','22728812775','Free Megabytes', 37911),('ahermontest1','D','LogicalDisk','22728812776','Split IO/Sec', 0)SELECT *FROM #tmpPIVOT(MAX(PMValue) FOR PM_Counter IN ( [Disk Write Bytes/Sec], [% Free Space], [Free Megabytes], [Split IO/Sec]))PDROP TABLE #tmp; |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-07 : 11:08:39
|
just another way isSELECT PMHost,PMInstance,PMObject,Timestamp,MAX(CASE WHEN PM_Counter = 'Disk Write Bytes/Sec' THEN PMValue END) AS [Disk Write Bytes/sec],MAX(CASE WHEN PM_Counter = '% Free Space' THEN PMValue END) AS [% Free Space],MAX(CASE WHEN PM_Counter = 'Free Megabytes' THEN PMValue END) AS [Free Megabytes],MAX(CASE WHEN PM_Counter = 'Split IO/Sec' THEN PMValue END) AS [Split IO/Sec]FROM #tmpGROUP BY PMHost,PMInstance,PMObject,Timestamp ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
mitin
Yak Posting Veteran
81 Posts |
Posted - 2013-03-07 : 11:34:24
|
Thanks James, your query only seems to work when * is being slected though. The table actually has a lot more columns that the ones I included in the example code, when i run the query with some specific fields in the 'select', then only those columns in the select are shown. and column related to the pivot part of the query are not.... |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-07 : 12:48:56
|
You don't have to use SELECT *. You will have all the columns except the two columns that are used as input to the PIVOT, and you will have all the pivoted columns. For example, see in red below. You can alias the columns as well - see red underlined below:CREATE TABLE #tmp (PMHost VARCHAR(32), PMInstance CHAR(1), PMObject varchar(32),Timestamp bigint, PM_Counter VARCHAR(32), PMValue INT);INSERT INTO #tmp VALUES('ahermontest1','C','LogicalDisk','22728812772','Disk Write Bytes/Sec',3816),('ahermontest1','D','LogicalDisk','22728812773','Disk Write Bytes/Sec', 1483),('ahermontest1','E','LogicalDisk','22728812774','% Free Space',74),('ahermontest1','C','LogicalDisk','22728812775','Free Megabytes', 37911),('ahermontest1','D','LogicalDisk','22728812776','Split IO/Sec', 0)SELECT PMHost, PMInstance, PMObject, Timestamp, [Disk Write Bytes/Sec], [% Free SPACE] AS [Free Space in Percent], [Free Megabytes], [Split IO/Sec]FROM #tmpPIVOT(MAX(PMValue) FOR PM_Counter IN ( [Disk Write Bytes/Sec], [% Free Space], [Free Megabytes], [Split IO/Sec]))PDROP TABLE #tmp; |
|
|
mitin
Yak Posting Veteran
81 Posts |
Posted - 2013-03-08 : 05:52:54
|
Thanks, but I want to be able to select all of the 'static' columns (probably not the right word to use) tha I need, for example I only want PMHst, PMInstance, PMObject and Timestamp but I also want all of the columns relating to the pivot part of the quer to be output, theres a lot of columns, so its impracical to include them all in the select....I only need SOME static columns but ALL of columns that are a result of the pivot.... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-08 : 06:00:36
|
you can include only columns you want in select statement. didnt understand whats the issue faced with that------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-08 : 07:11:52
|
If the issue is that you don't know in advance the columns that will be in the PIVOTed result, then you cannot use the PIVOT operator. You will need to use a dynamic PIVOT. See here: http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx |
|
|
|
|
|
|
|