Reformat Data (Different data from last post!)

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Reformat Data (Different data from last post!)

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	          PMValue
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

and I require it to be displayed as follows:


PMHost	    PMInstance	PMObject	Timestamp	Disk Write Bytes/sec	% Free Space	Free Megabytes	Split IO/Sec
ahermontest1	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 MVP
http://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
	#tmp
PIVOT(MAX(PMValue) FOR PM_Counter IN (
		[Disk Write Bytes/Sec],
		[% Free Space],
		[Free Megabytes],
		[Split IO/Sec]))P

DROP TABLE #tmp;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-07 : 11:08:39

just another way is


SELECT 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 #tmp
GROUP BY PMHost,PMInstance,PMObject,Timestamp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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
	#tmp
PIVOT(MAX(PMValue) FOR PM_Counter IN (
		[Disk Write Bytes/Sec],
		[% Free Space],
		[Free Megabytes],
		[Split IO/Sec]))P

DROP 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 MVP
http://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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources