Simple Talk is now part of the Redgate Community hub - find out why

Switching rows and columns in SQL

When they use SQL Server, one the commoner questions that Ms Access programmers ask is 'Where's the TRANSFORM/PIVOT command? So how do you swap colums and rows in an aggregate table? Do you really need to use a CLR routine for this?

Recently, I came across a description of a transformation operation using SQL Server CLR – the goal is to convert, or transpose, columns into rows and rows into columns. Microsoft ACCESS has a SQL TRANSFORM /PIVOT extension to SQL that automatically produces a crosstab query, and SQL Server 2005 introduced the pivot command, but neither simply swaps columns and rows. The problem was to replicate this sort of functionality in SQL Server. It’s possible to write a CLR function to transform the data, however, I believe that a set-based T-SQL query is the preferred solution to this type of problem.

In the example of the CLR transform function, the source data included columns for PaymentDate, and three types of payments:

917-image001.png

Once transformed the result would swap the columns and rows to this configuration:

917-image003.png

The following script creates the test table and populates it with the sample test data:

The root cause behind this transformation is that the three payment columns violate first normal form. Column names that include data (Medicare, Medicaid, VA) is clear indication that first normal form is being ignored with repeating columns. Had the source data been properly normalized there would been no need to transform the columns and rows. If you find yourself needing to perform this type of transformation regularly, then you have a problem with your schema design. A normalized version of the source table should have columns for PaymentDate, PaymentType, and Amount.

But assuming the denormalized data is all that’s available, Microsoft provides a straight-forward solution using the Pivot and UnPivot commands available since SQL Server 2005. The key is recognizing that the source data violated first normal form, and that the unpivot command is designed specifically to normalize this type of unnormalized data. In the query below, the sq subquery first uses an unpivot command to normalize the data which is then passed to the pivot command in the outer query. Pairing the unpivot and pivot command makes quick work of the transformation task. The only trick in the query is appending a “d” before each date so the pivot command will accept it as a column name:

We make some assumptions about the nature of the data when we use the MIN aggregate function. The routine expects to ignore duplicate entries for a particular PaymentDate if there are any.

Before SQL Server 2005, you would have had to use the slightly more convoluted code …

You’ll see that this would give different results from the first solution if there is duplicate data in the original aggregate table 

There have been several attempts to create a generic solution that emulates the TRANSFORM/PIVOT functionality ,(Rob Volk’s being probably the best known, Dynamic Cross-Tabs/Pivot Tables, but see also Creating cross tab queries and pivot tables in SQL) but the PIVOT/UNPIVOT functionality of SQL Server 2005 onwards makes such devices unnecessary.

The example provides two lessons for SQL server designers and developers. First – don’t underestimate the power of the set-based query. Turning to the CLR for a solution when T-SQL can solve the problem is nearly always an error. Perhaps a more significant lesion is that there’s a performance cost to denormalization when additional code is required to re-normalize the data. The programmer’s cliché, “Normalize till is hurts, then denormalize till it works,” simply isn’t true.

How you log in to Simple Talk has changed

We now use Redgate ID (RGID). If you already have an RGID, we’ll try to match it to your account. If not, we’ll create one for you and connect it.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue

Simple Talk now uses Redgate ID

If you already have a Redgate ID (RGID), sign in using your existing RGID credentials. If not, you can create one on the next screen.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue