Archive | October, 2013

Pivot – Basic Example

31 Oct

One of the most useful commands of late to myself is Pivot, introduce in SQL 2005. It is also one of the many commands which I completely fail to recall the syntax of. It sits in the dark room of ‘vague recollection’ in the further recesses of my mind, keeping company with Row_Number, pretty much everything to do with Replication and other such exciting things.

The concept of Pivot is simple – produce the contents of one column that is stored on several rows as several columns on one row.

For example, using a very simple table:

I have a query that returns the following results:

Pivot_01
Now I can use this but for my particular query I’ll get three times as many rows back as I want, because the data I have for this person is spread across three rows. What I really want is one row where all of the ‘Setting’ values are shown under ‘SettingtypeID’ columns:

Pivot_02

First of all, create the test table and data:


CREATE TABLE #DataToPivot( 
	PersonID		INT, 
	SettingTypeID	INT, 
	Setting			TINYINT 
  ); 

  INSERT INTO #DataToPivot 
          ( PersonID , 
            SettingTypeID, 
			Setting    
          ) 
  VALUES  ( 112233, 15 , 0 ), 
		  ( 112233, 16 , 1 ), 
		  ( 112233, 17 , 2 ); 
		  
 SELECT * 
 FROM #DataToPivot; 

There are a couple of obvious limitations with the Pivot command.
Firstly (using this example) I have to know what the values of SettingtypeID can be, because I need to create a column for ’15’, ’16’, ’17’ and any other values that I know this column can be.
Secondly, Pivot has to use an aggregate function for the manipulation of the data, so some thought is required for this. In the example the use MAX is acceptable but make sure you check your results when adapting this script to your purposes.

The code to produce the pivoted data is:

  SELECT PersonID, [15],[16],[17]
  FROM 
  (SELECT PersonID,
		  SettingTypeID,
		  Setting
  FROM #DataToPivot dtp
  ) AS x
  PIVOT
  (
	max(Setting) FOR SettingTypeID IN(  [15], [16], [17])
   )AS y;	

The query has three distinct parts to it:

  SELECT PersonID, [15],[16],[17]

This is how you want the results to appear. In this case the PersonID and then a column for each of the values of 15, 16 and 17. If there were any more possible values then additional columns with the appropriate values would be required.

  FROM 
  (SELECT PersonID,
          SettingTypeID,
          Setting
  FROM #DataToPivot dtp
  ) AS x

This is the subquery that the previous line will SELECT from (as well as the Pivot data). It is also where the following segment of code will extract from too:

  PIVOT
  (
	max(Setting) FOR SettingTypeID IN(  [15], [16], [17])
   )AS y;	

As the saying goes “This is where the magic happens”. Effectively – for every value specified for SettingtypeID (which is 15, 16 and 17 in this example) give me the maximum value of ‘Setting’.

It can get far more interesting than this example but this is a starting point.

Advertisements

Showing copyable SQL code in a blog

22 Oct

Well that was fun. Setting up the WordPress account was quite straightforward and there’s plenty of material out there about creating a basic post for it.
However, I wanted to be able to include SQL code that could be copied by anybody reading the article – much friendlier than making them type it back out themselves from a screen-shot.
It took a little bit of hunting and of course there is an article within the numerous WordPress support articles. The trick is to get the correct phrasing through Google.

I have three links to articles on this feature:
http://allaboutruby.wordpress.com/2009/06/26/post-ruby-or-html-css-sql-code-in-your-wordpress-com-blog/
http://en.support.wordpress.com/code/posting-source-code/
http://thesqlguy.wordpress.com/2011/06/20/publishing-source-code-to-wordpress-using-windows-live-writer-2011/
So to get the following code to appear in the correct colours and with the facility for it to be copied:
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE ‘%test%’
Place the correct tags around the code:

wordpress01

And it will appear as:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%test%'

Unfortunately, what I can’t find is any way to keep the formatting when using copy/paste into SSMS. Trying that produces the code on one long line. So for now, I’ll just have to try and remember to leave a space at the start of each line. Then at least the code will parse correctly (I’m not promising anything will actually execute correctly!).