{"id":76249,"date":"2017-12-12T15:04:12","date_gmt":"2017-12-12T15:04:12","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=76249"},"modified":"2019-01-22T17:30:01","modified_gmt":"2019-01-22T17:30:01","slug":"pivot-rotation-matrix-transpose-sql-server-new-method","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/pivot-rotation-matrix-transpose-sql-server-new-method\/","title":{"rendered":"Pivot Rotation and Matrix Transpose in SQL Server: A New Method?"},"content":{"rendered":"

The question often comes up: how do you pivot data in SQL Server? In looking closer at the question, there are two similar effects that are wanted. One is the equivalent to a pivot table in Excel, the other is a matrix transposition, where a table is simply rotated, as if it were an array, so that \u2026<\/p>\n

  1 2 3\r\n  4 5 6\r\n  7 8 9<\/pre>\n
\u2026 becomes \u2026<\/p>\n
  1 4 7\r\n  2 5 8\r\n  3 6 9<\/pre>\n
To put it a bit more formally,
\n \"\"<\/p>\n
These transposes used to be done by producing a normalised version of the first table, and then creating the transpose via a simple GROUP BY clause. Then came the PIVOT and UNPIVOT clause which remains probably the simplest way of doing it. Then came a few rather scary XML-based approaches that never caught on. Now, with JSON in SQL Server 2016, 2017, there is a new, versatile, approach.<\/p>\n
Let\u2019s start off by creating the test data, consisting of table representing a six by six array of random integers between one and a hundred. We\u2019ll create a type to do this, so this can be passed as a parameter to a function<\/p>\n
IF NOT EXISTS (SELECT * FROM sys.types AS T WHERE name LIKE 'MyMatrix') \r\n    CREATE TYPE dbo.MyMatrix AS TABLE \r\n  (\r\n       [row] INT IDENTITY NOT NULL PRIMARY KEY,\r\n  \t [1] INT NOT null, [2] INT NOT null, [3] INT NOT null, \r\n  \t [4] INT NOT null, [5] INT NOT null, [6] INT NOT null\r\n  )\r\n  go\r\n  DECLARE @MyMatrixTable MyMatrix\r\n  INSERT INTO @MyMatrixTable([1], [2], [3], [4], [5], [6])\r\n    SELECT [first]*100, [second]*100, third*100, fourth*100, fifth*100, sixth*100 \r\n    FROM (VALUES \r\n    (Rand(), Rand(),Rand(),Rand(),Rand(),Rand()),\r\n    (Rand(), Rand(),Rand(),Rand(),Rand(),Rand()),\r\n    (Rand(), Rand(),Rand(),Rand(),Rand(),Rand()),\r\n    (Rand(), Rand(),Rand(),Rand(),Rand(),Rand()),\r\n    (Rand(), Rand(),Rand(),Rand(),Rand(),Rand()),\r\n    (Rand(), Rand(),Rand(),Rand(),Rand(),Rand())\r\n    )RandomNumbers([first], [second], third, fourth, fifth, sixth)\r\n  SELECT * FROM @MyMatrixTable<\/pre>\n
<\/p>\n
Notice that we have inserted the row number. This is because tables have no intrinsic order, so we have to make it explicit.<\/p>\n
The Unpivot\/Pivot solution is fairly simple<\/p>\n
SELECT Transposed.TheRow as [row], [1], [2],[3],[4], [5], [6] from\r\n  (SELECT [row], [value], \r\n  TheRow\r\n   FROM  @MyMatrixTable UNPIVOT \r\n        (value FOR TheRow in ([1], [2], [3], [4], [5], [6]))f\r\n  )Norm\r\n  PIVOT\r\n  (Sum(value) FOR [row] IN ([1],[2],[3],[4],[5],[6])\r\n  )Transposed\r\n  ORDER BY TheRow<\/pre>\n
<\/p>\n
Before the PIVOT<\/strong> and UNPIVOT<\/strong> clause arrived, it was a lot clumsier and, in SQL Server 2016, and 2017 at least, takes twice as long.<\/p>\n
SELECT Norm.TheColumn AS row, \r\n      Sum(CASE WHEN Norm.TheRow = 1 THEN Norm.Value ELSE 0 END) AS [1], \r\n      Sum(CASE WHEN Norm.TheRow = 2 THEN Norm.Value ELSE 0 END) AS [2], \r\n      Sum(CASE WHEN Norm.TheRow = 3 THEN Norm.Value ELSE 0 END) AS [3], \r\n      Sum(CASE WHEN Norm.TheRow = 4 THEN Norm.Value ELSE 0 END) AS [4], \r\n      Sum(CASE WHEN Norm.TheRow = 5 THEN Norm.Value ELSE 0 END) AS [5], \r\n      Sum(CASE WHEN Norm.TheRow = 6 THEN Norm.Value ELSE 0 END) AS [6]\r\n    FROM\r\n      (\r\n      SELECT [@MyMatrixTable].[1], 1, [@MyMatrixTable].row FROM @MyMatrixTable\r\n      UNION ALL\r\n      SELECT [@MyMatrixTable].[2], 2, [@MyMatrixTable].row FROM @MyMatrixTable\r\n      UNION ALL\r\n      SELECT [@MyMatrixTable].[3], 3, [@MyMatrixTable].row FROM @MyMatrixTable\r\n      UNION ALL\r\n      SELECT [@MyMatrixTable].[4], 4, [@MyMatrixTable].row FROM @MyMatrixTable\r\n      UNION ALL\r\n      SELECT [@MyMatrixTable].[5], 5, [@MyMatrixTable].row FROM @MyMatrixTable\r\n      UNION ALL\r\n      SELECT [@MyMatrixTable].[6], 6, [@MyMatrixTable].row FROM @MyMatrixTable\r\n      ) AS Norm(Value, TheColumn, TheRow)\r\n    GROUP BY Norm.TheColumn \r\n    ORDER BY Norm.TheColumn;<\/pre>\n
With JSON, you have a great deal more freedom and, although the code looks messy, the operation is as fast as the Unpivot\/Pivot solution.<\/p>\n
DECLARE @JS NVARCHAR(MAX)\r\n  SELECT @JS=(SELECT [1], [2], [3], [4], [5], [6] FROM @MyMatrixTable FOR JSON AUTO)\r\n  SELECT [row],[1], [2], [3], [4], [5], [6] FROM (values\r\n  ( 1, Json_Value(@js,'$[0].\"1\"'), Json_Value(@js,'$[1].\"1\"'), Json_Value(@js,'$[2].\"1\"'), Json_Value(@js,'$[3].\"1\"'), Json_Value(@js,'$[4].\"1\"'), Json_Value(@js,'$[5].\"1\"')),\r\n  ( 2, Json_Value(@js,'$[0].\"2\"'), Json_Value(@js,'$[1].\"2\"'), Json_Value(@js,'$[2].\"2\"'), Json_Value(@js,'$[3].\"2\"'), Json_Value(@js,'$[4].\"2\"'), Json_Value(@js,'$[5].\"2\"')),\r\n  ( 3, Json_Value(@js,'$[0].\"3\"'), Json_Value(@js,'$[1].\"3\"'), Json_Value(@js,'$[2].\"3\"'), Json_Value(@js,'$[3].\"3\"'), Json_Value(@js,'$[4].\"3\"'), Json_Value(@js,'$[5].\"3\"')),\r\n  ( 4, Json_Value(@js,'$[0].\"4\"'), Json_Value(@js,'$[1].\"4\"'), Json_Value(@js,'$[2].\"4\"'), Json_Value(@js,'$[3].\"4\"'), Json_Value(@js,'$[4].\"4\"'), Json_Value(@js,'$[5].\"4\"')),\r\n  ( 5, Json_Value(@js,'$[0].\"5\"'), Json_Value(@js,'$[1].\"5\"'), Json_Value(@js,'$[2].\"5\"'), Json_Value(@js,'$[3].\"5\"'), Json_Value(@js,'$[4].\"5\"'), Json_Value(@js,'$[5].\"5\"')),\r\n  ( 6, Json_Value(@js,'$[0].\"6\"'), Json_Value(@js,'$[1].\"6\"'), Json_Value(@js,'$[2].\"6\"'), Json_Value(@js,'$[3].\"6\"'), Json_Value(@js,'$[4].\"6\"'), Json_Value(@js,'$[5].\"6\"'))\r\n  )f([row], [1], [2], [3], [4], [5], [6])<\/pre>\n
What I’m doing is simply converting the table into its JSON form, and then using this to create a table using the multi-row VALUES<\/strong>\u00a0 syntax which paradoxically allows expressions. The expression I’m using is JSON_Value<\/strong>, which allows me do effectively dictate the source within the table, via that JSON Path expression, and the destination. As it is an expression, I can do all sorts of manipulation as well as a transpose.\u00a0 I could, if I wanted, (in SQL 2017)provide that path parameter as a variable. This sort of technique can be used for several other reporting purposes, and it is well-worth experimenting with it because it is so versatile.<\/p>\n
Here are two function that do the rotation. I used them to check on performance. In my own timings they returned similar times when doing 1000 transposes.<\/p>\n
IF Object_Id('Dbo.JsonTranspose') IS NOT NULL\r\n     DROP function Dbo.JsonTranspose\r\n  GO\r\n  CREATE FUNCTION Dbo.JsonTranspose\r\n  \/**\r\n  Summary: >\r\n    Transposes a six by six matrix\r\n    by rotating it\r\n  Author: Phil Factor\r\n  Date: 11\/12\/2017\r\n  Database: MatrixTransform\r\n  Examples:\r\n     - Select * from Dbo.JsonTranspose((SELECT [1], [2], [3], [4], [5], [6] FROM @MyMatrixTable FOR JSON AUTO)) ORDER BY row\r\n  Returns: >\r\n     A 6*6 table of integers representing the transposed matrix\r\n          **\/\r\n    (\r\n    @js NVarchar(max)\r\n    )\r\n  RETURNS TABLE\r\n  AS\r\n  RETURN\r\n    (\r\n    SELECT [row],[1], [2], [3], [4], [5], [6] FROM (values\r\n    ( 1, Json_Value(@js,'$[0].\"1\"'), Json_Value(@js,'$[1].\"1\"'), Json_Value(@js,'$[2].\"1\"'), Json_Value(@js,'$[3].\"1\"'), Json_Value(@js,'$[4].\"1\"'), Json_Value(@js,'$[5].\"1\"')),\r\n    ( 2, Json_Value(@js,'$[0].\"2\"'), Json_Value(@js,'$[1].\"2\"'), Json_Value(@js,'$[2].\"2\"'), Json_Value(@js,'$[3].\"2\"'), Json_Value(@js,'$[4].\"2\"'), Json_Value(@js,'$[5].\"2\"')),\r\n    ( 3, Json_Value(@js,'$[0].\"3\"'), Json_Value(@js,'$[1].\"3\"'), Json_Value(@js,'$[2].\"3\"'), Json_Value(@js,'$[3].\"3\"'), Json_Value(@js,'$[4].\"3\"'), Json_Value(@js,'$[5].\"3\"')),\r\n    ( 4, Json_Value(@js,'$[0].\"4\"'), Json_Value(@js,'$[1].\"4\"'), Json_Value(@js,'$[2].\"4\"'), Json_Value(@js,'$[3].\"4\"'), Json_Value(@js,'$[4].\"4\"'), Json_Value(@js,'$[5].\"4\"')),\r\n    ( 5, Json_Value(@js,'$[0].\"5\"'), Json_Value(@js,'$[1].\"5\"'), Json_Value(@js,'$[2].\"5\"'), Json_Value(@js,'$[3].\"5\"'), Json_Value(@js,'$[4].\"5\"'), Json_Value(@js,'$[5].\"5\"')),\r\n    ( 6, Json_Value(@js,'$[0].\"6\"'), Json_Value(@js,'$[1].\"6\"'), Json_Value(@js,'$[2].\"6\"'), Json_Value(@js,'$[3].\"6\"'), Json_Value(@js,'$[4].\"6\"'), Json_Value(@js,'$[5].\"6\"'))\r\n    )f([row], [1], [2], [3], [4], [5], [6]) \r\n    )\r\n  GO\r\n  IF Object_Id('dbo.PivotUnpivotTranspose') IS NOT NULL\r\n     DROP function dbo.PivotUnpivotTranspose\r\n  GO\r\n  CREATE FUNCTION dbo.PivotUnpivotTranspose\r\n  \/**\r\n  Summary: >\r\n    Use pivot\/unpivot to\r\n    transpose a matrix\r\n  Author: Phil Factor\r\n  Date: 11\/12\/2017\r\n  Database: MatrixTransform\r\n  Examples:\r\n     - Select [row], [1], [2], [3], [4], [5], [6] FROM dbo.PivotUnpivotTranspose(@MyMatrixTable) ORDER BY row\r\n  Returns: >\r\n    A 6*6 table of integers representing the transposed matrix\r\n          **\/\r\n    (\r\n    @TheMatrix My6x6Matrix readonly\r\n    )\r\n  RETURNS TABLE\r\n  AS\r\n  RETURN\r\n    (\r\n    SELECT Transposed.TheRow as [row], [1], [2],[3],[4], [5], [6] from\r\n    (SELECT [row], [value], \r\n    TheRow\r\n     FROM  @TheMatrix UNPIVOT \r\n          (value FOR TheRow in ([1], [2], [3], [4], [5], [6]))f\r\n    )Norm\r\n    PIVOT\r\n    (Sum(value) FOR [row] IN ([1],[2],[3],[4],[5],[6])\r\n    )Transposed\r\n    )\r\n  GO<\/pre>\n
The JSON_Value<\/strong> and JSON_Modify<\/strong> now, in SQL Server 2017 and in Azure SQL Database, allow you to provide a variable as the value of path, rather than just a literal. This would provide several opportunities for producing reports.<\/p>\n","protected":false},"excerpt":{"rendered":"
The question often comes up: how do you pivot data in SQL Server? In looking closer at the question, there are two similar effects that are wanted. One is the equivalent to a pivot table in Excel, the other is a matrix transposition, where a table is simply rotated, as if it were an array,……<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[5134],"coauthors":[6813],"class_list":["post-76249","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-sql-prompt"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/76249","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\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=76249"}],"version-history":[{"count":10,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/76249\/revisions"}],"predecessor-version":[{"id":76307,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/76249\/revisions\/76307"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=76249"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=76249"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=76249"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=76249"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}