If you need to build a very complex query that returns a matrix with dynamic columns and rows from T-SQL,  you might want to take a look at this new project posted on CodePlex :

SQL Server 2-D Matrix Builder


  Attachment: image001.jpg (34666 bytes)

This project is basically a SQLCLR stored procedure called TheMatrix, as well as a SQLCLR UDT called QueryBuilder that allow you to describe all the queries you want to call to build the axis and the content of the matrix you need to retrieve back.

Check the CodePlex site for more information : http://www.codeplex.com/SQL2DMatrixBuilder