Pivot your data in SQL

SQL has a neat feature that allows you to pivot data in a table. Effectively converting rows to columns, columns to rows. It is not that often that I’ve had to use this feature in the databases that I’ve worked on in the past – but recently, a client had a very specific need to do this.

For those of you familiar with the data structure of Sage 300 (formerly Sage ERP Accpac), the client wished to see which item codes did not have an optional field attached to it. They needed this in a way that would allow a clean view of the data for a dashboard they were wanting to create in Tableau.

The data in the item’s optional field table (ICITEMO) looks like this (using sample data, not actual client data):

What the client wants to be able to see is the data pivoted – one line for each item code and all the optional fields and their values as columns; which looks like this:

Note that the above image shows an additional optional field “VOLUME” which is null. The client had requested that they want to know which optional fields setup in ICOFD are missing from ICITEMO. A NULL value here shows that the optional field is missing from ICITEMO.

Now, the trick here isn’t just pivoting the data. If I did a simple pivot, I would only get the optional fields that are already attached to the item code and not the ones that are not. I also wanted to be able to dynamically lookup the optional field setup table and set them up as columns at runtime. I wanted to do this so that if the client decided to add additional optional fields in the future, they wouldn’t need to wait for me to update the data source in SQL to make the necessary changes.

For the solution, I decided to create the data source as a stored procedure. They’re fast, easy to code and very flexible. They can also be used as a data source in Business Objects’ Crystal Reports and Tableau.

DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
/*
    This will get us our optional fields set up in IC Optional Fields (ICOFD)
    and store them in our temporary string variable @cols.
*/
SELECT 
  @cols = STUFF((
    SELECT ‘,’ + QUOTENAME(OPTFIELD)
    FROM ICOFD
    GROUP BY OPTFIELD
    ORDER BY OPTFIELD
    FOR XML PATH(”), TYPE
  ).value(‘.’, ‘NVARCHAR(MAX)’), 1, 1,”)

/*
    This part dynamically builds our select query, using the column
    (optional fields) fetched above and performs the pivot.
*/
SET @query = N’SELECT ITEMNO, ‘ + @cols + N’ FROM
(
  SELECT ITEMNO, OPTFIELD, [VALUE]
  FROM ICITEMO
) x
PIVOT
(
  MAX([VALUE])
  FOR OPTFIELD IN (‘ + @cols + N’)
) p ‘

/* This is an SQL system stored procedure that will execute our string query */
exec sp_executesql @query;