Introduction

This project is all about bringing a 2-D matrix builder to Microsoft SQL Server 2005 (or later). So why do we need this? Consider the following schema:

Now, what we would like to get as a query result is the following :

Notice that the headers are not columns names but the records available in the
Table_B table. So how do you get this natively with Microsoft SQL Server? Well, you can't! Unless you use the
SQL Server 2-D Matrix Builder.

This project is all about using the hosted .NET Common Langage Runtime in Microsoft SQL Server. All you have to do is uploading the
TheMatrixSqlClr.dll assembly into your SQL Server instance and use it. The next two chapters describe how to install this assembly into your SQL Server instance and how to reuse
TheMatrix stored procedure.

Documentation

The QueryBuilder Used-Defined Type allows you to specify what query should be executed to fill:

The first column rows (Query 1)

The columns headers (Query 2)

The Matrix content (Query 3)

Once you have declared a variable of this type, you can invoke any of its static methods using the
:: operator. If you intend to call a literal SQL query, use the NewQueryText method. If you plan to use a stored procedure instead, use the
NewStoredProcedure method.

-- If you are using a literal SQL query
Declare @MyLiteralSQLQueryBuilder QueryBuilder
Set @MyLiteralSQLQueryBuilder::New_QueryText(N'Select MyColumn1, MyColumn2, MyColumn3 From MyTable1 Inner Join... Where MyColumn4 = @MyParameter')

-- If you are using a stored procedure
Declare @MyStoredProcedureQueryBuilder QueryBuilder
Set @MyStoredProcedureQueryBuilder::New_StoredProcedure(N'MyStoredProcedureName')

If your literal SQL query or your stored procedure need some input parameters, you can supply their values by calling the
SetParameter method:

For the Matrix content query only, we need to infere the type of the columns that are returned in the
resultset. Although, this product tries to infere this type, it might not be suitable in all cases. Therefore, you can help infere this type by calling one of the following method:

SetContentType(typeName)

SetContentType2(typeName, maxLength)

SetContentType3(typeName, precision, scale)

typeName must be one of the System.Data.SqlDbType enumeration value:

Finally, once you have correctly declared the 3 QueryBuilder variables you need, you can call
TheMatrix stored procedure.

The last thing we need to check for our three queries is the following:

Query 1 and Query 2 need to return two columns (you can return more than two columns but only two columns will be used):

one column must be the "Primary Key"

one column must be the "Display Value"

Query 3 needs to return three columns (you can return more than three columns but only three columns will be used):

one column must be the "Row Primary Key"

one column must be the "Column Primary Key"

one column must be the "Display Value" of the Matrix

We also need to supply two values:

The "Display Value" for the first column header

The "Display Value" to use if we have a missing value in the Matrix content returned by
Query 3

Those parameters will be supplied directly to TheMatrix stored procedure call.