T-SQL: Converting Rows to Columns with SQL Scripting
In database design it can be nice to have a very flexible database for logging or configuration of resources. This flexibility is obtained by normalizing your database design to the third normal form (3NF) or beyond. One situation that may come up is that a table may have key/value pairs that link to another table and you would like to produce a SQL query which aligns the keys as columns and the values below those columns. I’ve created a very flexible logging scenario that shows this in action.
The following log table is an example of what you might see in any major system requiring SOX compliant logging. The key thing to note is that we are not keeping track of what was logged, only that an action occurred, what action it was, and who submitted it.
|1||2010-06-25 10:53:10||Allan Bogh||Create User|
|2||2010-06-25 10:55:33||Allan Bogh||Update User|
Dynamic Log Table
This table references the Log table and keeps track of all of the information regarding what happened. The table structure is rather simple with a LogID, FieldName, and FieldValue. The FieldName column can be anything from any type of log entry. For instance, a Create User might need FirstName, but an Update User might need OldFirstName.
Sarbanes-Oxley says that an IT administrator needs a way to report on the actions taken in an environment. For this, we need to take this highly flexible database and produce a single line for each log entry which includes all the information regarding that log item. Since this report will be a culmination of all of the data in the logging tables, it will also include the columns from the other actions, however these columns must be NULL. To do this, I’ve created a custom SQL script which pulls the required column information out of the DynamicLog table, then produces a report based on the columns. This SQL script can be loaded into a stored procedure to enhance it or help speed it up by caching the query. The query has the possibility of slowing down as the database gets large, so data cleanup may be necessary over time.
-- Gets all of the unique columns from our database DECLARE Cur CURSOR FOR SELECT DISTINCT FieldName FROM dbo.DynamicLog DECLARE @CurFieldName nvarchar(50) DECLARE @AllColumns nvarchar(max) DECLARE @Sql nvarchar(max) -- Variable to store column SQL code SET @AllColumns = '' -- Opens the cursor for use OPEN Cur -- Get the columns from the LogDynamic table -- Store column in @AllColumns FETCH NEXT FROM Cur INTO @CurFieldName WHILE @@FETCH_STATUS = 0 BEGIN SET @AllColumns = @AllColumns + '(SELECT FieldValue FROM DynamicLog DL WHERE DL.LogID=L.ID AND DL.FieldName=''' + @CurDBField + ''' AND NOT FieldValue IS NULL) AS ' + @CurFieldName + ',' FETCH NEXT FROM Cur INTO @CurFieldName END -- Kills the cursor Close Cur DEALLOCATE Cur SET @AllColumns = SUBSTRING(@AllColumns,0,LEN(@AllColumns)) /* Strip last comma */ SET @Sql = 'SELECT L.[ID], L.DateSubmitted, L.SubmittedBy, L.LogAction,'+ @AllColumns+' FROM dbo.[Log] L /* Only select from logging, nothing else. */' EXEC sp_executesql @Sql
This produces a resulting SQL query that looks like this:
SELECT L.[ID], L.DateSubmitted, L.SubmittedBy, L.LogAction, (SELECT FieldValue FROM DynamicLog DL WHERE DL.LogID=L.ID AND DL.FieldName='FirstName' AND NOT FieldValue IS NULL ) AS FirstName, (SELECT FieldValue FROM DynamicLog DL WHERE DL.LogID=L.ID AND DL.FieldName='LastName' AND NOT FieldValue IS NULL ) AS LastName FROM dbo.[Log] L /* Only select from logging, nothing else. */
Using the SQL Query defined in the script or stored procedure above, you can create a table which looks like the one below. Any columns not defined for a particular action are intentionally left NULL.
|1||2010-06-25 10:53:10||Allan Bogh||Create User||John||Doe||NULL|
|2||2010-06-25 10:55:33||Allan Bogh||Update User||Jake||NULL||John|