{"id":77556,"date":"2018-03-13T14:25:55","date_gmt":"2018-03-13T14:25:55","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=77556"},"modified":"2026-04-14T13:03:21","modified_gmt":"2026-04-14T13:03:21","slug":"easier-way-transposing-query-result-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/development\/dotnet-development\/easier-way-transposing-query-result-sql-server\/","title":{"rendered":"Transposing SQL Server Query Results with SQLCLR: Dynamic Row-to-Column Rotation"},"content":{"rendered":"

This article presents a SQLCLR-based approach to transposing SQL Server query results – rotating rows to columns dynamically without knowing column names in advance. The solution is a .NET stored procedure (MATRIX.Transposing) that accepts any query as a parameter and returns the transposed result set. Unlike SQL Server’s built-in PIVOT operator, which requires column names to be specified at design time, this approach handles variable column counts determined at runtime. For the companion JSON-based approach, see An Easier Way of Pivoting Data in SQL Server.<\/strong><\/p>\n

I like Phil Factor\u2019s articles. The latest one, An Easier Way of Pivoting Data in SQL Server<\/a>, inspired me to start exploring another option to transpose a result from a query.<\/p>\n

There are many situations in which the results of a query look better when they are rotated. For example, when you execute the simple T-SQL SELECT * FROM sys.databases<\/strong> to get all databases on the current SQL Server instance, the results look better somehow rotated. In my opinion, the results look better rotated if the number of columns is greater than the number of rows.<\/p>\n

In this article, I will show you how to accomplish this task by introducing a SQLCLR stored procedure MATRIX.Transposing<\/strong> that does all the magic with rotation. (NOTE: I am using the words transpose and rotation interchangeably as synonyms throughout the article.)<\/p>\n

Therefore, if you would like to transpose the results of your query, you will run a query like that in the listing below once the solution is in place.<\/p>\n

EXEC MATRIX.TRANSPOSING @query = 'SELECT * FROM SYS.DATABASES';<\/pre>\n
The result of SELECT * FROM sys.databases<\/strong> is displayed in the image below. The column on which rotation will take place after running the new code\u00a0 is outlined with red. (NOTE: The column is name<\/strong>.)<\/p>\n
\"\"<\/p>\n
After transposing (executing the query from the listing above ), you will get the results as shown in the image below. The name<\/strong> values now become the column names. The properties of each database are listed vertically instead of horizontally. \u00a0\u00a0<\/p>\n
<\/p>\n
In short, T-SQL is not suitable enough to transpose the query result. There is an option with pivoting which includes aggregation and only moves the values from one column to column headings. It doesn\u2019t flip everything as shown here. However, to accomplish such a task of transposing without aggregation, you have to process your results row by row. That is not what T-SQL is designed for. Yes, you can use a cursor or maybe you can accomplish this task by using an XML transformation. I wrote maybe because, to be honest, I never tested such possibilities. So, in my opinion, there is no easy way to do it.<\/p>\n
To run Phil\u2019s solution, you must be running at least SQL Server 2016 version and make sure that the database is in 130 compatibility mode. Yes, I learned a lot about JSON support in SQL Server from his article, but that didn\u2019t keep me from exploring other possibililties.<\/p>\n
A SQLCLR Solution<\/h2>\n
Another possibility, as you might guess, is to use .NET. It means building a SQLCLR project. At first, you must enable CLR if not already enabled. To do this, execute the T-SQL script in the listing below.<\/p>\n
  -- Enable & Check CLR\n  sp_configure N'clr enabled', 1\n  GO\n  RECONFIGURE\n  GO\n  SELECT\n      sc.*\n  FROM sys.configurations AS sc\n  WHERE sc.[name] = N'clr enabled'<\/pre>\n
For those who do not want to play with .NET source code, there is a ZIP file in the attachment of the article. In the file, you can find two T-SQL scripts. One is for deploying the solution named Setup.sql<\/strong> and the other is for cleaning the environment after playing with the solution named CleanUp.sql<\/strong>.<\/p>\n
Open Setup.sql<\/strong> in SSMS, just press F5<\/strong>, and the solution is deployed. I tested the solution on all SQL Server versions, starting with SQL 2005 until SQL 2017. (NOTE: To deploy to 2017, you will have to save the snk file and modify the path and password before running.)<\/p>\n
Also, you can download the complete solution from GitHub<\/a> and deploy the solution through Visual Studio. After deploying the solution from VS or running setup.sql<\/strong>, your database model will be extended with a newly created stored procedure named MATRIX.Transposing <\/strong>(NOTE: MATRIX is the database schema). In either case, you will need to modify the @Path<\/strong> and @Password<\/strong> variables for your environment.<\/p>\n
The examples in this article will use the AdventureWorks2014 database. To check your installation, expand the Programmability node to be sure the stored procedure is there as well as the SQLCLR assembly, as shown in the image below.<\/p>\n
<\/p>\n
The stored procedure takes several parameters. In the image below, I outlined the mandatory parameter in red.<\/p>\n
<\/p>\n
Yes, the @Query<\/strong> parameter is the query whose results we would like to transpose.<\/p>\n
Stored Procedure Parameters<\/h2>\n
Attentive readers will notice that some parameters do not show a default value in SSMS, which is opposite to my statement that only one parameter is mandatory. In my opinion, it is a bug in SSMS. Run the following T-SQL query in order to check to see if there is a default value associated with the other parameters. This statement queries sys.objects<\/strong> and sys.parameters<\/strong> filtering by the stored procedure name.<\/p>\n
  SELECT SCHEMA_NAME(SCHEMA_ID) AS [Schema],\n         SO.name AS [ObjectName],\n         SO.Type_Desc AS [ObjectType (UDF\/SP)],\n         P.parameter_id AS [ParameterID],\n         P.name AS [ParameterName],\n         TYPE_NAME(P.user_type_id) AS [ParameterDataType],\n         p.has_default_value,\n         P.default_value\n  FROM sys.objects AS SO\n       INNER JOIN sys.parameters AS P ON SO.OBJECT_ID = P.OBJECT_ID\n  WHERE SO.OBJECT_ID IN\n  (\n      SELECT OBJECT_ID\n      FROM sys.objects\n      WHERE SCHEMA_NAME(SCHEMA_ID) = 'MATRIX'\n            AND NAME = 'Transposing'\n  )\n  ORDER BY [Schema],\n           SO.name,\n           P.parameter_id;\n  GO<\/pre>\n
The results are shown in the image below. You can see that the @Query<\/strong> parameter is the only one that does not have a default value.<\/p>\n
<\/p>\n
SSMS performs well when displaying standard T-SQL stored procedure parameters. It seems that SSMS is a little bit confused when has to display SQLCLR stored procedure parameters.<\/p>\n
Now back to the main topic: in the table below, each parameter is explained in detail.<\/p>\n\n\n\n\n\n\n\n\n\n\n
\n
Parameter Name<\/strong><\/p>\n<\/td>\n
\n
Parameter Description<\/strong><\/p>\n<\/td>\n
\n
Parameter Type<\/strong><\/p>\n<\/td>\n
\n
Default Value<\/strong><\/p>\n<\/td>\n
\n
Can be null<\/strong><\/p>\n<\/td>\n<\/tr>\n<\/thead>\n
\n
@Query<\/p>\n<\/td>\n
\n
Query or stored procedure, which result we will transpose. Calling a stored procedure always should begin with keyword EXEC<\/p>\n<\/td>\n
\n
Nvarchar(max)<\/p>\n<\/td>\n
\n
No default value<\/p>\n<\/td>\n
\n
No<\/p>\n<\/td>\n<\/tr>\n
\n
@Params<\/p>\n<\/td>\n
\n
Query or stored procedure parameters<\/p>\n<\/td>\n
\n
Nvarchar(4000)<\/p>\n<\/td>\n
\n
NULL<\/p>\n<\/td>\n
\n
Yes<\/p>\n<\/td>\n<\/tr>\n
\n
@Rco<\/p>\n<\/td>\n
\n
Rotate\u00a0column\u00a0ordinal<\/p>\n<\/td>\n
\n
Smallint<\/p>\n<\/td>\n
\n
0<\/p>\n<\/td>\n
\n
No<\/p>\n<\/td>\n<\/tr>\n
\n
@KeyValueOption<\/p>\n<\/td>\n
\n
Do we specify custom headers with transposing<\/p>\n<\/td>\n
\n
Smallint<\/p>\n<\/td>\n
\n
0<\/p>\n<\/td>\n
\n
No<\/p>\n<\/td>\n<\/tr>\n
\n
@ColumnMapping<\/p>\n<\/td>\n
\n
Custom header(s), column names are separated by a comma.<\/p>\n<\/td>\n
\n
Nvarchar(4000)<\/p>\n<\/td>\n
\n
NULL<\/p>\n<\/td>\n
\n
Yes<\/p>\n<\/td>\n<\/tr>\n
\n
@TableName<\/p>\n<\/td>\n
\n
If we like to save result into permanent table or temp table<\/p>\n<\/td>\n
\n
Nvarchar(256)<\/p>\n<\/td>\n
\n
NULL<\/p>\n<\/td>\n
\n
Yes<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Solution Deployment<\/h2>\n
In order to explore the solution, start up Visual Studio Community Edition (or any kind of licensed edition). I am using Visual Studio 2017, but the solution works fine with Visual Studio 2013 as well. The solution uses SQL Server Database Project<\/em> as the template.<\/p>\n
In the solution, there are several T-SQL scripts. The SQLCLR framework allows you to define only one T-SQL statement for pre-deployment. It must be executed before the assembly is published. Similarly, there is a post-deploy script that is executed after the assembly is published.<\/p>\n
The pre-deployment script, named PreDeployment.sql<\/strong>, first creates the MATRIX<\/strong> schema and then depending on SQL Server version (only for SQL Server 2017+), it creates an asymmetric key based on the solution snk file. Then it creates a login and grants the unsafe assembly permission to that login.<\/p>\n
In SQL Server 2017, there is a new instance level setting named clr strict security<\/em>. By default, it is turned on, and it is not recommended to turn it off. This setting requires strong signing assembly, and that was not the case prior SQL Server 2017 version if we marked the project as SAFE<\/em>. Therefore, to use this solution as a whole, I included support for signing the assembly by using the asymmetric key.<\/p>\n
The pre-deployment script is located in PreDeployment.sql<\/strong>. You can see the whole script in the listing below.<\/p>\n
  --------------------------------------------------------------------------------------\n  --Create schema if not exists\n  --------------------------------------------------------------------------------------\n  IF NOT EXISTS\n  (\n      SELECT schema_name\n      FROM information_schema.schemata\n      WHERE schema_name = 'MATRIX'\n  )\n      BEGIN\n          EXEC sp_executesql\n               N'CREATE SCHEMA MATRIX';\n      END;\n  ---------------------------------------------------------------------------------------\n  --- Only for SQL Server 2017+\n  -----------------------------------------------\n  IF SERVERPROPERTY('productversion') >= '14' AND\n    SUBSTRING(CAST(SERVERPROPERTY('productversion') as nvarchar(10)),1,1) != '9'\n   \n  BEGIN\n  --------------------------------------------------------------------------------------\n  --Create\u00a0asymmetric\u00a0key\u00a0\n  --\n  --!Replace\u00a0the\u00a0path\u00a0'D:\\VS2017_PROJECTS\\TransposingMatrix\\TransposingMatrix\\'\u00a0with\u00a0your\u00a0path\n  --!Replace\u00a0password\u00a0with\u00a0more\u00a0appropriate\u00a0for\u00a0your\u00a0situation.!!!!!!!!!!!!!!!!!!!!!!!-------\n  --\n  --------------------------------------------------------------------------------------\n  DECLARE\u00a0@path\u00a0nvarchar(260)\u00a0=\u00a0\n      N'D:\\VS2017_PROJECTS\\TransposingMatrix\\TransposingMatrix\\askTransposingMatrix.snk'\n  DECLARE\u00a0@password\u00a0nvarchar(128)\u00a0=\u00a0N'S#im@ple1Tal0k'\n  DECLARE\u00a0@tsqlToEval\u00a0as\u00a0nvarchar(max)\u00a0=\u00a0N'USE\u00a0MASTER;'\u00a0+\u00a0CHAR(13)\u00a0+\u00a0\n  \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0'CREATE\u00a0ASYMMETRIC\u00a0KEY\u00a0[askTransposingMatrix]'\u00a0+\u00a0char(13\u00a0)\u00a0+\u00a0\n  \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0'FROM\u00a0FILE\u00a0=\u00a0'''\u00a0+\u00a0@path\u00a0+\u00a0'''\n  \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0ENCRYPTION\u00a0BY\u00a0PASSWORD\u00a0=\u00a0'''+\u00a0@password+\u00a0'''';\n  \u00a0\n  --PRINT\u00a0@tsqlToEval\n   \n  \u00a0\u00a0\u00a0\u00a0IF\n  \u00a0\u00a0\u00a0\u00a0(\n  \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0SELECT\u00a0COUNT(*)\n  \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0FROM\u00a0master.sys.asymmetric_keys\n  \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0WHERE\u00a0name\u00a0LIKE\u00a0'askTransposingMatrix%'\n  \u00a0\u00a0\u00a0\u00a0)\u00a0=\u00a00\n  \u00a0\u00a0\u00a0\u00a0BEGIN\n  \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0EXEC\u00a0sp_executesql\u00a0@tsqlToEval;\n  \u00a0\u00a0\u00a0\u00a0END;\n  \u00a0\u00a0\u00a0\u00a0IF\u00a0NOT\u00a0EXISTS\n  \u00a0\u00a0\u00a0\u00a0(\n  \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT\u00a0loginname\n  \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0FROM\u00a0master.dbo.syslogins\n  \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0WHERE\u00a0name\u00a0=\u00a0'loginTransposingMatrix'\n  \u00a0\u00a0\u00a0\u00a0)\n  \u00a0\u00a0\u00a0\u00a0BEGIN\n  \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0DECLARE\u00a0@sqlStatement\u00a0AS\u00a0NVARCHAR(1000);\n   \u00a0\u00a0\u00a0SELECT\u00a0@SqlStatement\u00a0=\u00a0'CREATE\u00a0LOGIN\u00a0[loginTransposingMatrix]\u00a0\n                          FROM\u00a0ASYMMETRIC\u00a0KEY\u00a0askTransposingMatrix';\n  \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0EXEC\u00a0sp_executesql\u00a0\u00a0@SqlStatement;\n  \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0EXEC\u00a0sp_executesql\n  \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0N'USE\u00a0MASTER;\n  \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0GRANT\u00a0UNSAFE\u00a0ASSEMBLY\u00a0TO\u00a0[loginTransposingMatrix];';\n  \u00a0\u00a0\u00a0\u00a0END;\n  END;<\/pre>\n
The post-deployment script transfers the resulting stored procedure from the default DBO <\/strong>schema to newly created MATRIX<\/strong> schema and creates some defaults. You can see the post-deployment script in the listing below.<\/p>\n
The post-deployment script is located in the PostDeployment.sql<\/strong> file. You can see the entire script in the listing below.<\/p>\n
  ALTER\u00a0PROCEDURE\u00a0[dbo].[Transposing]\n  \t@Query\u00a0[nvarchar](max),\n  \t@Params\u00a0[nvarchar](4000)=NULL,\n  \t@Rco\u00a0[smallint]=1,\n  \t@KeyValueOption\u00a0[smallint]=0,\n  \t@ColumnMapping\u00a0[nvarchar](4000)\u00a0=\u00a0NULL,\n  \t@TableName [nvarchar](128) = NULL\n  AS\u00a0EXTERNAL\u00a0NAME\u00a0[SimpleTalk.SQLCLR.Matrix].[StoredProcedures].[Transposing];\n  GO\n  IF\u00a0EXISTS\u00a0(\u00a0SELECT\u00a0*\u00a0\n  \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM\u00a0\u00a0\u00a0sysobjects\u00a0\n  \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE\u00a0\u00a0id\u00a0=\u00a0object_id(N'[MATRIX].[Transposing]')\u00a0\n  \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0and\u00a0OBJECTPROPERTY(id,\u00a0N'IsProcedure')\u00a0=\u00a01\u00a0)\n  BEGIN\n  \u00a0\u00a0\u00a0\u00a0DROP\u00a0PROCEDURE\u00a0[MATRIX].[Transposing]\n  END\n  ALTER\u00a0SCHEMA\u00a0[MATRIX]\u00a0TRANSFER\u00a0[dbo].[Transposing];\n  --Transfer\u00a0function\n  IF\u00a0EXISTS\u00a0(\u00a0SELECT\u00a0*\u00a0\n  \t\t\tFROM\u00a0\u00a0\u00a0sysobjects\u00a0\n  \t\t\tWHERE\u00a0\u00a0id\u00a0=\u00a0object_id(N'[MATRIX].[Help]')\u00a0\n  \t\t\t\t\u00a0\u00a0\u00a0and\u00a0type\u00a0=\u00a0N'FT'\u00a0)\n  BEGIN\n  \tDROP\u00a0FUNCTION\u00a0[MATRIX].[Help]\n  END\n  ALTER\u00a0SCHEMA\u00a0\u00a0[MATRIX]\u00a0TRANSFER\u00a0dbo.Help;<\/pre>\n
The .NET Solution<\/h2>\n
The query and its parameters are passed as parameters to the MATRIX.Transposing<\/strong> stored procedure. The code uses classic ADO.NET techniques to get an object of type Dataset<\/strong>. For those who are new to .NET, a Dataset<\/strong> should be considered as a table collection. When a dataset is formed, then it is easy to perform many types of transformations. The possible transformations include rotation, e.g., switching rows with columns.<\/p>\n
In the solution, getting the data is accomplished in DataAccess.cs<\/strong>. It uses an almost classic ADO.NET approach. The most interesting thing here is the so-called context connection. According to Microsoft documentation, the context connection allows you to execute T-SQL statements in the same context that was used when your code was invoked in the first place. To obtain the context connection, you must use the context connection<\/strong> string keyword. The listing below shows how to get the dataset.<\/p>\n
  public static DataSet GetDataSet(string Query, bool isSp, SqlParameter[] listOfParams, \n       ref string errorText)\n          {\n              DataSet ds = new DataSet();\n              try\n              {\n                  using (SqlConnection cnn = new SqlConnection(\"context connection=true\"))\n                  {\n                      using (SqlCommand command = new SqlCommand(Query, cnn))\n                      {\n                          cnn.Open();\n                          if (isSp)\n                              command.CommandType = CommandType.StoredProcedure;\n                          if (listOfParams != null)\n                          {\n                              foreach (SqlParameter p in listOfParams)\n                              {\n                                  command.Parameters.Add(p);\n                              }\n                          }\n                          using (SqlDataAdapter sqlAdp = new SqlDataAdapter())\n                          {\n                              sqlAdp.SelectCommand = command;\n                              sqlAdp.Fill(ds);\n                          }\n                          cnn.Close();\n                      }\n                  }\n              }\n              catch (Exception ex)\n              {\n                  errorText += ex.Message;\n              }\n              return ds;\n          }<\/pre>\n
To explain the rest of the parameters, let us take the Phil example. (NOTE: To run it, you must install objects from his article.)<\/p>\n
  SELECT\n      *\n  FROM OPENJSON((SELECT\n  \t   dbo.TransposedJSONMatrix((SELECT\n  \t\t\t *\n  \t\t  FROM (VALUES\n  \t\t  (25119, 25002, 25109, 23860, 22957, 23518, 23330, 22926, 23365, \n                        23418, 23644),\n  \t\t  --'Total North America'\n  \t\t  (5332, 5504, 5780, 6057, 6054, 6384, 6624, 6782, 7035, \n                        7190, 7083),\n  \t\t  --'Total S. & Cent. America\n  \t\t  (20213, 20426, 20166, 20086, 19276, 19223, 19075, 18605, 18372, \n                        18266, 18380),\n  \t\t  --'Total Europe & Eurasia'\n  \t\t  (6576, 6711, 6935, 7440, 7855, 8201, 8455, 8770, 9011, \n                        9353, 9570),\n  \t\t  --'Total Middle East',6576\n  \t\t  (2917, 2928, 3063, 3236, 3315, 3486, 3413, 3579, 3678, \n                        3763, 3888),\n  \t\t  --'Total Africa',2917,2928\n  \t\t  (24569, 25157, 26035, 25900, 26244, 27954, 28893, 30001, 30588, \n                        31119, 32444)\n  \t\t  --'Total Asia Pacific'\n  \t\t  ) oilConsumption ([2005], [2006], [2007], [2008], [2009], [2010], \n                       [2011], [2012], [2013], [2014], [2015])\n  \t\t  FOR JSON AUTO)\n  \t   ))\n  )\n  WITH (\n  [Year] INT '$.Col0', --remember that these are the column names of the original table\n  [Total North America] INT '$.Col1',\n  [Total S. & Cent. America] INT '$.Col2',\n  [Total Europe & Eurasia] INT '$.Col3',\n  [Total Middle East] INT '$.Col4',\n  [Total Africa] INT '$.Col5',\n  [Total Asia Pacific] INT '$.Col6'\n  );<\/pre>\n
The result will look similar to what is shown in the image below.<\/p>\n
<\/p>\n
In order to get the same result as in Phil\u2019s example, you must call the MATRIX.Transposing<\/strong> stored procedure as shown in the listing bellow.<\/p>\n
  EXEC\u00a0[MATRIX].[Transposing]\u00a0@Query\u00a0=\u00a0'SELECT\n  \t\u00a0*\n  \u00a0\u00a0FROM\u00a0(VALUES\n  \u00a0\u00a0(25119,\u00a025002,\u00a025109,\u00a023860,\u00a022957,\u00a023518,\u00a023330,\u00a022926,\u00a023365,\u00a023418,\u00a023644),\n  \u00a0\u00a0(5332,\u00a05504,\u00a05780,\u00a06057,\u00a06054,\u00a06384,\u00a06624,\u00a06782,\u00a07035,\u00a07190,\u00a07083),\n  \u00a0\u00a0(20213,\u00a020426,\u00a020166,\u00a020086,\u00a019276,\u00a019223,\u00a019075,\u00a018605,\u00a018372,\u00a018266,\u00a018380),\n  \u00a0\u00a0(6576,\u00a06711,\u00a06935,\u00a07440,\u00a07855,\u00a08201,\u00a08455,\u00a08770,\u00a09011,\u00a09353,\u00a09570),\n  \u00a0\u00a0(2917,\u00a02928,\u00a03063,\u00a03236,\u00a03315,\u00a03486,\u00a03413,\u00a03579,\u00a03678,\u00a03763,\u00a03888),\n  \u00a0\u00a0(24569,\u00a025157,\u00a026035,\u00a025900,\u00a026244,\u00a027954,\u00a028893,\u00a030001,\u00a030588,\u00a031119,\u00a032444)\n  \u00a0\u00a0)\u00a0oilConsumption\u00a0([2005],\u00a0[2006],\u00a0[2007],\u00a0[2008],\u00a0[2009],\u00a0[2010],\u00a0[2011],\u00a0[2012],\n         \u00a0[2013],\u00a0[2014],\u00a0[2015])'\n  \t\t\t\u00a0,@keyvalueoption\u00a0=\u00a01\n  \t\t\t\u00a0,@columnMapping\u00a0=\u00a0N'[Year],[Total\u00a0North\u00a0America],\n                               [Total\u00a0S.\u00a0&\u00a0Cent.\u00a0America],[Total\u00a0Europe\u00a0&\u00a0Eurasia],\n                               [Total\u00a0Middle\u00a0East],[Total\u00a0Africa],[Total\u00a0Asia\u00a0Pacific]';<\/pre>\n
The key point here is to pass @columnMapping<\/strong> as a new table header and to set @keyValueOption<\/strong> to 1. If you set @keyValueOption<\/strong> to 0, @columnMapping<\/strong> is ignored, and the result will look like the image below.<\/p>\n
<\/p>\n
The first row, from the earlier test, is now the header of our table. In the solution, there are two kinds of transformations which are determined by the parameter @keyValueOption<\/strong>.<\/p>\n
If you specify @keyValueOption<\/strong> to 1 and submit @ColumnMapping<\/strong> as NULL, the result is shown in the image below.<\/p>\n
<\/p>\n
The generic header is displayed, highlighted with yellow in the image above. The first column is named Key<\/strong>, and the others are Value<\/strong>, Value1<\/strong>, and so on.<\/p>\n
It\u2019s easy to transform the DataTable<\/strong> object as shown in the listing below. All code connected with transposing the DataTable<\/strong> is located in TableManipulation.cs<\/strong>.<\/p>\n
  public static DataTable RotateTableWithKeyValue(DataTable oldTable,\n           string columnMapping)\n         {\n             DataTable newTable = new DataTable();\n             DataRow dr = default(DataRow);\n   \n   \n             if ( columnMapping == null )\n             {\n                 newTable.Columns.Add(\"Key\");\n                 int no = 0;\n                 foreach (DataRow row in oldTable.Rows)\n                 {\n                     newTable.Columns.Add(\"Value\" + (no == 0 ? \"\" :\n                          no.ToString().Trim()));\n                     no++;\n                 }\n             }\n             else\n             {\n                 string[] names = columnMapping.Split(',');\n                 foreach (string s in names)\n                 {\n                     newTable.Columns.Add(s);\n                 }\n   \n                 if ( names.Length < oldTable.Rows.Count+1)\n                 {\n                     int no = names.Length;co\n                     while (no < oldTable.Rows.Count+1)\n                     {\n                         newTable.Columns.Add(\"Value\" + no.ToString().Trim());\n                         no++;\n                     }\n                 }\n   \n   \n             }\n             for (int col = 0; col <= oldTable.Columns.Count - 1; col++)\n             {\n   \n                 dr = newTable.NewRow();\n   \n   \n                 dr[0] = oldTable.Columns[col].ColumnName;\n   \n   \n                 for (int row = 0; row <= oldTable.Rows.Count - 1; row++)\n                 {\n                     dr[row + 1] = oldTable.Rows[row][col];\n                 }\n   \n                 newTable.Rows.Add(dr);\n             }\n   \n             return newTable;\n         }<\/pre>\n
When you transpose the original data and produce another table, the code must send the results. Sending results is where SQLCLR magic \u2018comes to light.\u2019<\/p>\n
The whole task is accomplished by utilizing the SqlPipe<\/strong> class. SqlPipe<\/strong> allows managed stored procedures running in process in a SQL Server database to return results to the caller. This class cannot be inherited.<\/p>\n
First is invoked SendResultsStart<\/strong>. SendResultStart<\/strong> marks the beginning of a result set to be sent back to the client and uses the record parameter to construct the metadata that describes the result set. For now, ignore the record parameter for a while.<\/p>\n
SendResultStart<\/strong> comes in a pair with SendResultEnd<\/strong>. As you probably expect SendResultEnd<\/strong> marks the end of a result set and returns the SqlPipe instance to the initial state. The code that pipes the data table to the client is located in PipeUtilities.cs<\/strong>.<\/p>\n
  public static void PipeDataTable(DataTable dt)\n  {\n   \n    SqlMetaData[] md = ExtractDataTableColumnMetaData(dt);\n    SqlDataRecord r = new SqlDataRecord(md);\n    SqlPipe p = SqlContext.Pipe;\n    p.SendResultsStart(r);\n    try\n    {\n      foreach (DataRow row in dt.Rows)\n      {\n         for (int i = 0; i < r.FieldCount; i++)\n         {\n            object v = row[i];\n            if (null != v)\n            { \n              v = v.ToString();\n            }\n            r.SetValue(i, v);\n        }\n   \n        p.SendResultsRow(r);\n       }\n      }\n      finally\n      {\n         p.SendResultsEnd();\n      }\n  }<\/pre>\n
SendResultRow<\/strong>, as the name implies, sends a single row of data back to the client. Also, to conclude the part of sending the result back to the client, SqlDataRecord<\/strong> represents a single row of data and its metadata. This class cannot be inherited.<\/p>\n
You are probably asking yourself if the solution limited to newest SQL Server version? To answer this question let\u2019s take a look at the image below. Outlined in red is the project properties. The project uses .NET 2.0. It means that you can use the solution starting with SQL Server 2005<\/strong>!<\/p>\n
<\/p>\n
There are a few things to write about the solution. Help is available at any point by executing<\/p>\n
SELECT\n  \t*\n  FROM MATRIX.Help('MATRIX.Transposing');<\/pre>\n
The result is shown in the image below<\/p>\n
<\/p>\n
Solution Limitations<\/h2>\n
The solution has some limitations. There is a maximum number of columns that you can have per SQL Server table; it could be up to 30000 columns. This solution is limited to 1000. If you try to transpose a table with more than 1000 rows, a warning is displayed in the message window, and you get first 1000 rows transposed. It means you get the table with 1000 columns.<\/p>\n
For example, if we execute T-SQL like in the listing below<\/p>\n
EXEC MATRIX.Transposing @Query = N'SELECT * FROM Person.Person';<\/pre>\n
To transpose table \u2018Person.Person\u2019 which has about 20K records, you will get the warning like in the image below.<\/p>\n
<\/p>\n
However, you will get the result, as shown in the image below.<\/p>\n
<\/p>\n
In this spot, I have to make a small digression and describe the term of an application domain. This is extremely important for understanding the performance of the stored procedure.<\/p>\n
An application domain is a construct in the Microsoft .NET Framework common language runtime (CLR) that is the unit of isolation for an application. When a user asks for some functionality located in your assembly the first time, an application domain is created. The application domain is a lightweight process used to enforce isolation between running .NET code within the same SQLOS process. SQLCLR uses application domains to isolate execution of .NET code on a per database and per assembly owner basis<\/strong>.<\/p>\n
This process of loading an application domain takes time (this is a performance penalty). Therefore, the first execution of your SQLCRL function is always much slower than the next execution.<\/p>\n
So, let us get back to the main topic. If the application domain is loaded, I get the result, transpose first 1000 rows in 3 seconds. If the application domain is not loaded everything takes a little bit longer. Maybe, in the future, I will remove this limitation and create some configuration table in which users can decide what is a maximum number of rows to transpose. But in this place, you have to ask yourself why you need a table of thousands of columns?<\/p>\n
In the solution, there are some interesting tests which are located in the Test.sql<\/strong> file.<\/p>\n
Execute the T-SQL script in the listing below. In the script, the table is filtered passing two parameters and the WHERE clause.<\/p>\n
  EXEC [MATRIX].[Transposing] @Query = N'SELECT SalesOrderID , salesordernumber , \n     purchaseordernumber , OrderDate , ShipDate , SubTotal , TaxAmt , TotalDue\n  FROM Sales.SalesOrderHeader\n  WHERE SalesOrderId >= @salesId1 AND SalesOrderId <= @salesId2;\n  '\n  \t ,@Params = N'@salesId1 int=43665,@salesId2 int=43671';<\/pre>\n
You can see the result in the image below. The filter is applied successfully.<\/p>\n
<\/p>\n
Also, what we should do with our results? SSMS has powerful capabilities to copy the result and paste, for example, into an Excel file. Furthermore, the results could be saved into a temporary table or permanent table as is shown in the listing below.<\/p>\n
  EXEC MATRIX.TRANSPOSING @query = 'SELECT * FROM sys.databases'\n  \t\t\t\t   ,@tableName = N'##tempTable';\n  --We could pass permanent table as well\n  --EXEC MATRIX.TRANSPOSING @query = 'SELECT * FROM sys.databases'\n  --\t\t\t\t   ,@tableName = N'MATRIX.PERMANENT';\n  ---The same result as in the first query\n  SELECT\n      *\n  FROM ##tempTable;<\/pre>\n
The key point here was to pass a @tableName<\/strong> parameter. It could be a permanent or temporary table name. The result is shown in the image below. Notice the result of the second query is equal to the result of the first query.<\/p>\n
<\/p>\n
Finally, take a look at the @rco<\/strong> parameter. Execute the T-SQL displayed in the listing below.<\/p>\n
  EXEC MATRIX.Transposing @Query = N'SELECT * FROM sys.databases'\n  \t\t\t\t   ,@Rco = 0;\n  EXEC MATRIX.Transposing @Query = N'SELECT * FROM sys.databases'\n  \t\t\t\t   ,@Rco = 1;<\/pre>\n
The first and the second T-SQL statement differ by the @Rco<\/strong> parameter. In the first statement the @Rco<\/strong> value is 0 and in the second statement is 1. You can notice in the image below that in the first case the header is formed based on the database name. In the second case, the header is formed based on database ID.<\/p>\n
<\/p>\n
If you woul like to clean up your environment after playing with this solution, there is a script for this purpose, located in Setup directory as well as in the attachment of the article.<\/p>\n
Summary<\/h2>\n
Phil\u2019s articles are not just educational but also inspiring. Without Phil, I would never start investigating how to do transposing the query result. In the article, I showed you how to use SQLCLR to accomplish this task. The solution works fine on SQL Server 2005+.<\/p>\n
Although SQLCLR seems to be a second-class citizen on SQL Server, there are situations when could replace T-SQL. It is worth exploring and using in solving practical problems.<\/p>\n
The solution source code can be found on GitHub here.<\/a><\/p>\n\n\n
\n    
FAQs: An Easier Way of Transposing Query Result in SQL Server<\/h2>\n\n                        
1. How do I transpose rows to columns in SQL Server without using PIVOT?<\/h3>\n            
\n                
Use the SQLCLR stored procedure MATRIX.Transposing described in this article, or the JSON-based TransposedJSONMatrix function from the companion Pivot article. Both approaches handle dynamic column counts. For a T-SQL-only solution without CLR, dynamic SQL with PIVOT can approximate the result but requires building the column list as a string and executing it with sp_executesql.<\/p>\n            <\/div>\n                    
2. What is the MATRIX.Transposing stored procedure in SQL Server?<\/h3>\n            
\n                
MATRIX.Transposing is a SQLCLR stored procedure deployed as part of a .NET assembly. It accepts a SQL query string as a parameter and returns the transposed result – rows become columns and columns become rows. The procedure uses ADO.NET internally to execute the query, reads the result set into a DataTable, transposes it in .NET memory, and returns the rotated result to SQL Server. Deployment requires CLR to be enabled and the assembly to be registered.<\/p>\n            <\/div>\n                    
3. What are the limitations of the SQLCLR transpose approach?<\/h3>\n            
\n                
The MATRIX.Transposing procedure has a column count limit (SQL Server tables support up to 1,024 non-sparse columns per table). If the input query returns more rows than the column limit after transposition, the result will be truncated. The procedure also requires CLR to be enabled and appropriate assembly permissions. For very large result sets, memory consumption on the .NET side may be a concern since the full result is held in a DataTable before transposition.<\/p>\n            <\/div>\n                    
4. When should I use SQLCLR transpose versus the JSON matrix approach?<\/h3>\n            
\n                
Use SQLCLR (MATRIX.Transposing) when you need the transposition to work as a stored procedure call that returns a result set directly, or when you are on SQL Server 2014 or earlier where JSON support is not available. Use the JSON-based TransposedJSONMatrix function (from the Pivot article) when you prefer a T-SQL-only approach without CLR overhead, are on SQL Server 2016 or later, or need to embed the transposition in a larger query or view.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
Transpose SQL Server query results dynamically using a SQLCLR stored procedure – no need to know column names in advance. Covers CLR assembly deployment, stored procedure parameters, solution limitations, and the .NET ADO.NET implementation behind the matrix rotation.…<\/p>\n","protected":false},"author":314795,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":true,"footnotes":""},"categories":[143538,143531,1],"tags":[95509],"coauthors":[47071],"class_list":["post-77556","post","type-post","status-publish","format-standard","hentry","category-dotnet-development","category-t-sql-programming-sql-server","category-uncategorized","tag-standardize"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/77556","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/314795"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=77556"}],"version-history":[{"count":16,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/77556\/revisions"}],"predecessor-version":[{"id":109612,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/77556\/revisions\/109612"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=77556"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=77556"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=77556"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=77556"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}