SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Reformat Data (Different data from last post!)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mitin
Yak Posting Veteran

81 Posts

Posted - 03/07/2013 :  08:39:31  Show Profile  Reply with Quote
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!

Edited by - mitin on 03/07/2013 08:49:33

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/07/2013 :  09:10:38  Show Profile  Reply with Quote
use pivot

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 03/07/2013 :  09:50:30  Show Profile  Reply with Quote
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...
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 03/07/2013 :  10:02:08  Show Profile  Reply with Quote
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;	
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/07/2013 :  11:08:39  Show Profile  Reply with Quote
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/

Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 03/07/2013 :  11:34:24  Show Profile  Reply with Quote
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....
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 03/07/2013 :  12:48:56  Show Profile  Reply with Quote
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;	
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 03/08/2013 :  05:52:54  Show Profile  Reply with Quote
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....
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/08/2013 :  06:00:36  Show Profile  Reply with Quote
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/

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 03/08/2013 :  07:11:52  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000