How SQL Server CLR and C# Can Solve a Common Problem

A common complaint I hear from my development team is, “Why can’t T-SQL do that?” As a member of a small development shop for a sizable healthcare company, the line is often blurred between my official role as DBA and my ad-hoc role as database developer. I bridge the gap between our web/report developers’ skill set and the latest offerings from SQL Server’s ever-increasing tool set, demonstrating how a new feature lets you approach a problem differently and more efficiently. One such SQL Server 2005 feature is the SQL Common Language Runtime (CLR) in which SQL objects can be developed in the .NET Framework, utilizing robust languages such as C# and VB.NET. I've gravitated to C#, which I believe is the more intuitive language.

The C# language augments T-SQL’s set-based strengths by providing procedural efficiencies and expanded data structures such as multi-dimensional arrays. How many times have you found yourself creating temp tables with cursors in T-SQL to mimic the utility of an array? This article demonstrates how leveraging the CLR and C# arrays can easily provide a solution to a common report requirement which is otherwise cumbersome to develop in T-SQL: query result-set transposition.

The Need to Transpose Data

The company I work for provides healthcare to the elderly. The financial/clinical data and subsequent enterprise reports are not only critical to the well being of the company but are necessary to ensure the high-quality care our patients deserve. Often, this data is date-driven, meaning the query displays the dates in a column; however, the report layout requires that the dates be displayed in the first row as the column header, as Figure 1 shows. It's quickly apparent that the report simply needs the data from the source query to be flipped so that the rows become the columns and the columns become the rows. In other words, we need to transpose the data.

In Microsoft Excel, this process is simple: Either use the Transpose function or paste-transpose the data. However, in T-SQL, there isn't an easy method using built-in objects to accomplish a transposition. Like many database shops, we eagerly tried out SQL Server 2005's new T-SQL PIVOT and UNPIVOT features, and although we found great utility with their use, the PIVOT/UNPIVOT process doesn't solve the transpose question: We aren't pivoting data into aggregation buckets. Herein lies our developers’ complaint: “Why can’t we simply transpose data in T-SQL, just switching rows and columns, as we can do in Excel?”

The process seems simple enough, but I can understand why Microsoft doesn't have a transpose function in T-SQL. Because row data will mostly not be of the same data type, the transposed column data type can't be determined without some assumptions of the output. So, rather than wait for Microsoft to provide a solution, why not use the tools we have to create our own transpose solution? If the data could be stored in an array, where manipulation is granular, the transposition would be straightforward. The CLR and C# offers flexibility not found in T-SQL.

Using the SQL CLR and C# to Transpose Data

Our goal is to create a general solution that takes any query the caller has permission context to execute and transpose the result set. Our solution will be a CLR stored procedure written in C# which accepts a single string parameter of the caller’s query and returns a transposed result set. The overall strategy for solving this problem can be used as a template to attack general CLR data problems:

After you've mastered this basic use of the CLR, you have the basis and subsequent confidence to tackle a whole new world of complex data questions. When faced with a problem, your solution environment is no longer confined to T-SQL but is expanded to the CLR toolsets.

Examining a Simple Transpose

Figure 2 shows a simple query and a transposition of its result set. When considering the result set of a query, we typically don't consider the column header as a row. However, in this example, we observe that the column names are also involved in the transpose and are therefore, for our purposes, considered a row. As a consequence, our 3-by-4 result set is transposed to a 4-by-3 result set.

The data from the query is stored in the two-dimensional array, queryData[i, j], where the i index references the row location and the j index references the column location. Note that array-elements are zero-based referenced: The first position is referenced by 0, the second position by 1, the third by 2, and so on. Therefore, the expression queryData[1,5] refers to the data value located at the intersection of the second row (1) and the sixth column (5).



With the data stored in the queryData array, we can now follow the transpose of a single element using the array notation, as you see in Figure 3. The data element 10.78 is contained in dataArray[1,2]. When the element is transposed, it moves to the zero-based location of [2,1]. If the transposed data is stored in a new array transposedData[i, j], we have the relationship that Figure 4 shows. Notice that the indices are simply switched. To generalize this, tranposedData[i, j] = queryData[j, i], which is the basis of our solution

Assumptions Made for the Solution

Often, assumptions are made in the problem to narrow the scope of a solution. In this case, two assumptions are made:

The CLR/C# Transpose Stored Procedure

Using the general strategy that I've described, the diagram in Figure 6 outlines the program workflow. The code is divided in to five sections:

Listing 1 (below) provides the C# code in its entirety. We bypass the variable declarations and focus on the remaining sections.

Section 2: Executing Caller's Query and Storing the Data

In this section, the caller’s query is executed and the result set is stored in an array. The first order of business is to open a connection to the SQL service: First create a SqlConnection object, then create a SqlCommand object using the caller’s query and the connection object, as you see in Callout A. The connection then opens. Take note of the connection string context connection=true. With this connection, the query is executed in the same context of the caller, in both location and security.

Next, the query is executed and is assigned to a SqlDataReader object. When the ExecuteReader method is called, the query execution begins, but data isn't read. Before we begin reading the data rows, column metadata such as count, names, and data type can be obtained; the FieldCount method is used to determine the column count, and the GetName method is used for the column names, as you see in Callout B.

Using the Read method of the reader object, the queryData array is filled with the result set, as you see in Callout C. To maintain the total number of rows, the rowCount variable increments each time a row is read.

Once the outer loop is finished reading the rows, the queryData array is now populated with the query’s result set. To recap what is known, the first row of the queryData array contains the column names, the remaining array rows contain the queried data, rowCount holds row count (plus 1 to account for the column-header row), and columnCount holds the column count of the result set.

Section 3: Transposing the Data

The next order of business is to transpose the data into the new array transposedData. To help with readability, new row and column count variables are created and used to size the new transposedData array, as you see in Callout D. The new row count is the old column count, and the new column count is the old row count.

Loop through the queryData array, transposing the data into tranposedData. The outer loop transverses the rows and the inner loop transverses the columns, as you see in Callout E.

The transpose is achieved with the tranposedQueryData[i, j] = queryData[j, i] statement that was gleaned from the “Examining a Simple Transpose” section. The transposedData array now contains the transposed data.

Section 4: Outputting the Transposed Result Set Back to the Caller

We're now ready to output, or pipe, the result set back to the caller. The basic strategy for constructing a result set back to the caller is as follows:

Utilizing this strategy, construct the SqlMetaData array and define each column, looping through the column names, as you see in Callout F. This is where the VARCHAR(100) assumption is used in the SqlDbType.VarChar, maxDataSize reference . Remember, the first row of the transposedData[0,j] array contains the transposed column names.

Now, construct the SqlDataRecord and start the output, as you see in Callout G. Loop through the row and columns of the transposedData array, and send the row to the caller, as you see in Callout H. Finally, end the result set and send the caller the message Transpose complete. Messages are sent to the caller using the SqlContext.Pipe.Send method, as you see in Callout I.

Section 5: Handle Errors

With CLR programming, it's important to handle errors gracefully and meaningfully. Errors from this procedure typically stem from a malformed input query. In the example that Figure 7 shows, the s is missing from the sys.objects table reference.



The Try/Catch construction handles any errors that might occur during the entire transpose process, as you see in Callout J. The CLR Transpose procedure is now complete, and control is returned to the Caller.

Compile and Deploy the Transpose CLR Stored Procedure

Deploying the Transpose CLR stored procedure to your database can be summarized by three steps: Compile the C# code to a DLL library file, create an assembly in the database using the DLL, and create the CLR stored procedure referencing the assembly. The Visual Studio 2005/2008 Professional edition makes life easy for you by performing all three steps within the Visual Studio environment. However, if you don't own the Professional edition, you can use the free Microsoft C# Visual Studio Express edition to compile your code; the remaining two steps are T-SQL commands executed on your database. The download files associated with this article will guide you through the exercise.

Using the Transpose Stored Procedure

The syntax for the Transpose stored procedure is

EXEC Transpose @query = [query to be transformed]

If the query contains single quotes to denote strings, substitute the single quotes with two single quotes, as Figure 8 shows.


Experiment with the various types of queries: simple, relational, and distributed. Also, intentionally create mistakes in the query syntax to view how the Try/Catch handles errors.

Extend Your T-SQL Environment

This Transpose procedure provides a simple exercise in using the SQL CLR to extend your T-SQL environment to solve a common problem that's otherwise cumbersome to solve in traditional T-SQL. Once you've mastered executing queries, processing data, and generating result sets within the CLR/C# environment, you'll gain the confidence to tackle more complex problems.

Listing 1: The CLR/C# Transpose Stored Procedure

using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;public partial class MyStoredProcedureClass{[Microsoft.SqlServer.Server.SqlProcedure]public static void Transpose(SqlString queryParameter){// SECTION 1: Variable declarations//integersint maxNumberofRows = 2048; int rowCount = 1;int columnCount = 0;  int transposedRowCount = 0;  int transposedColumnCount = 0; int maxDataSize =100;//stringsstring callersQuery = queryParameter.ToString(); string[,] queryData;string[,] transposedData;// .NET SQL objects. These objects will get instantiated later in the code. SqlConnection conn;SqlCommand comm;SqlDataReader dataReader;SqlMetaData[] transposedColumns;SqlDataRecord rowRecord;try{// SECTION 2 : Execute Caller's query and store dataBEGIN CALLOUT Aconn = new SqlConnection("context connection=true;");comm = new SqlCommand(callersQuery, conn);conn.Open();END CALLOUT ABEGIN CALLOUT BdataReader = comm.ExecuteReader();columnCount = dataReader.FieldCount;queryData = new string[maxNumberofRows, columnCount];for (int j = 0; j < columnCount; j++) {queryData[0,j] = dataReader.GetName(j);}END CALLOUT BBEGIN CALLOUT Cwhile (dataReader.Read()) {for (int j= 0;j < columnCount; j++){queryData[rowCount,j] = dataReader[j].ToString();}rowCount++;}dataReader.Close();conn.Close();END CALLOUT C// SECTION 3:  Transpose the dataBEGIN CALLOUT DtransposedRowCount = columnCount;transposedColumnCount = rowCount;transposedData = new string[transposedRowCount, transposedColumnCount];END CALLOUT DBEGIN CALLOUT Efor (int i = 0; i < transposedRowCount; i++){for (int j = 0; j < transposedColumnCount; j++){transposedData[i, j] = queryData[j,i];}}END CALLOUT E// SECTION 4: Ouput the data back to CallerBEGIN CALLOUT FtransposedColumns = new SqlMetaData[transposedColumnCount];for (int j = 0; j < transposedColumnCount; j++){transposedColumns[j]= new SqlMetaData(transposedData[0, j], SqlDbType.VarChar, maxDataSize);}END CALLOUT FBEGIN CALLOUT GrowRecord = new SqlDataRecord(transposedColumns);SqlContext.Pipe.SendResultsStart(rowRecord);END CALLOUT GBEGIN CALLOUT Hfor (int i = 1; i < transposedRowCount; i++) {for (int j = 0; j < transposedColumnCount; j++) {rowRecord.SetSqlString(j, transposedData[i, j]);}SqlContext.Pipe.SendResultsRow(rowRecord);}END CALLOUT HBEGIN CALLOUT ISqlContext.Pipe.SendResultsEnd();SqlContext.Pipe.Send("Transpose complete.");END CALLOUT I}// SECTION 5: Handle errorsBEGIN CALLOUT Jcatch (Exception e){SqlContext.Pipe.Send("There was a problem. Exception Report: ");SqlContext.Pipe.Send(e.Message.ToString());END CALLOUT J}return;}};