Notes on SQL

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:

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:

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.