{"id":110557,"date":"2026-05-20T12:00:00","date_gmt":"2026-05-20T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=110557"},"modified":"2026-05-13T15:56:28","modified_gmt":"2026-05-13T15:56:28","slug":"demystifying-pivot-and-unpivot-in-t-sql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/demystifying-pivot-and-unpivot-in-t-sql\/","title":{"rendered":"Demystifying PIVOT and UNPIVOT in T-SQL"},"content":{"rendered":"\n<p>There are few operators in T-SQL that cause developers to scramble for documentation more than <code><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/questions-about-pivoting-data-in-sql-server-you-were-too-shy-to-ask\/\" target=\"_blank\" rel=\"noreferrer noopener\">PIVOT<\/a><\/code> and <code>UNPIVOT<\/code>. Beyond documentation, transforming columns into rows (and vice-versa) can often be confusing and frustrating for those of us tasked with reformatting data for use by an application.<\/p>\n\n\n\n<p>This article walks through <code>PIVOT<\/code> and <code>UNPIVOT<\/code>, providing examples of simple use cases for both &#8211; as well as some more complex scenarios we can run into in real-world data. These can be extraordinarily useful ways to reformat data efficiently and quickly with less code than the alternatives. So, there is no need to fear them again!<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-where-are-the-t-sql-pivot-and-unpivot-operators-used\">Where are the T-SQL PIVOT and UNPIVOT operators used? <\/h2>\n\n\n\n<p>Both the <code>PIVOT<\/code> and <code>UNPIVOT<\/code> operators are unique in that they are long-standing inclusions in the <a href=\"https:\/\/www.red-gate.com\/simple-talk\/collections\/how-to-code-in-t-sql\/\" target=\"_blank\" rel=\"noreferrer noopener\">T-SQL<\/a> surface area, but are not found in <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/ansi-sql\/\" target=\"_blank\" rel=\"noreferrer noopener\">ANSI-SQL<\/a> or many of the other popular flavors of SQL. <\/p>\n\n\n\n<p><code>PIVOT<\/code> and <code>UNPIVOT<\/code> are included in:<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/\" target=\"_blank\" rel=\"noreferrer noopener\">SQL Server <\/a><br><br><\/li>\n\n\n\n<li><a href=\"https:\/\/azure.microsoft.com\/en-us\/products\/azure-sql\/database\" target=\"_blank\" rel=\"noreferrer noopener\">Azure SQL Database<\/a><br><br><\/li>\n\n\n\n<li>Synapse Analytics<br><br><\/li>\n\n\n\n<li>SQL Database in <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/what-is-microsoft-fabric-all-about\/\" target=\"_blank\" rel=\"noreferrer noopener\">Fabric<\/a><\/li>\n<\/ul>\n<\/div>\n\n\n<h2 class=\"wp-block-heading\" id=\"h-why-do-developers-struggle-with-pivot-and-unpivot\">Why do developers struggle with PIVOT and UNPIVOT?<\/h2>\n\n\n\n<p>Despite being ubiquitous in T-SQL, developers are often confused or intimidated by the syntax. Being functions that are not often used, it\u2019s easy to forget how to use them and struggle to piece together a solution using Microsoft Learn, articles, and trial-and-error. It&#8217;s equally easy to avoid these functions in favor of other solutions that involve more frequently-used syntax.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-usage-and-examples-of-pivot-in-t-sql\">Usage and examples of PIVOT in T-SQL<\/h2>\n\n\n\n<p><strong>The most common usage of <code>PIVOT<\/code> is during analytics or reporting when there is a need to convert row-based data into column-based data for output or further processing.<\/strong> <\/p>\n\n\n\n<p>Consider a set of weekly sales data in <em>WideWorldImporters<\/em> for the following query:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\n\t[Invoice Date Key],\n\t[Customer Key],\n\tQuantity\nFROM Fact.Sale\nWHERE [Invoice Date Key] &gt;= '3\/8\/2015'\nAND [Invoice Date Key] &lt;= '3\/14\/2015'\nORDER BY [Customer Key];<\/pre><\/div>\n\n\n\n<p>The results show sales quantities per customer per day:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"431\" height=\"377\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-27.png\" alt=\"Image of results showing sales quantities per customer per day.\" class=\"wp-image-110558\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-27.png 431w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-27-300x262.png 300w\" sizes=\"auto, (max-width: 431px) 100vw, 431px\" \/><\/figure>\n\n\n\n<p>This is a simple data set with only three columns to manage. Consider a request for analytic data where there&#8217;s a need to have one row per customer with a column for each date, and total quantity sold on each. You&#8217;re provided with a sample format like this:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"853\" height=\"160\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-28.png\" alt=\"The sample format you're provided with.\" class=\"wp-image-110559\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-28.png 853w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-28-300x56.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-28-768x144.png 768w\" sizes=\"auto, (max-width: 853px) 100vw, 853px\" \/><\/figure>\n\n\n\n<p>The blank boxes in the report are the total quantity sold on a given date for a customer. This is a natural use-case for <code>PIVOT<\/code>, as we want to convert <a href=\"https:\/\/www.red-gate.com\/simple-talk\/blogs\/guidelines-for-choosing-data-types\/\" target=\"_blank\" rel=\"noreferrer noopener\">row-based data into column-based data<\/a> while summing up quantities along the way.<\/p>\n\n\n\n<section id=\"my-first-block-block_86d884bfe887783190eb1e0bd581a4e6\" class=\"my-first-block alignwide\">\n    <div class=\"bg-brand-600 text-base-white py-5xl px-4xl rounded-sm bg-gradient-to-r from-brand-600 to-brand-500 red\">\n        <div class=\"gap-4xl items-start md:items-center flex flex-col md:flex-row justify-between\">\n            <div class=\"flex-1 col-span-10 lg:col-span-7\">\n                <h3 class=\"mt-0 font-display mb-2 text-display-sm\">Fast, reliable and consistent SQL Server development&#8230;<\/h3>\n                <div class=\"child:last-of-type:mb-0\">\n                                            &#8230;with SQL Toolbelt Essentials. 10 ingeniously simple tools for accelerating development, reducing risk, and standardizing workflows.                                    <\/div>\n            <\/div>\n                                            <a href=\"https:\/\/www.red-gate.com\/products\/sql-toolbelt-essentials\/\" class=\"btn btn--secondary btn--lg\" aria-label=\"Learn more &amp; try for free: Fast, reliable and consistent SQL Server development...\">Learn more &amp; try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n<p><\/p>\n\n\n\n<p><strong>The basic format of <code>PIVOT<\/code> is as follows:<\/strong><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\n   [Output Column List]\nFROM (\n   [Select Query]) AS SourceData\nPIVOT (\n  AggregateFunction([Column to Aggregate])\n   FOR [Column to Pivot]\n   IN ([Value List])\n) AS PivotData<\/pre><\/div>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"h-following-that-formula-the-following-are-the-components-needed-to-write-our-pivot-query\">Following that formula, the following are the components needed to write our <code>PIVOT<\/code> query:<\/h4>\n\n\n\n<p><strong>[Column List]<\/strong>: We will use * for this example, though you can customize as needed (as shown later.)<br><\/p>\n\n\n\n<p><strong>[Select Query]<\/strong>: This will be the same query we ran earlier, minus the <code>ORDER BY<\/code> clause. This will usually be similar to the already-identified\/unmodified original query that retrieves the required data.<br><\/p>\n\n\n\n<p><strong>AggregateFunction<\/strong>: We want to add up the quantities per customer per date, so this will be <code>SUM<\/code>. <strong>If the source data doesn&#8217;t require <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/sql-data-aggregation-aggravation\/\" target=\"_blank\" rel=\"noreferrer noopener\">aggregation<\/a>, an aggregate function must still be provided here<\/strong>! In those scenarios, simply use whatever aggregate function is simplest, and add a comment to indicate that its choice does not matter.<br><\/p>\n\n\n\n<p><strong>[Column to Aggregate]:<\/strong> Since we are summing up <em>Quantity<\/em>, that&#8217;ll be the column here.<br><\/p>\n\n\n\n<p><strong>[Column to Pivot]<\/strong>: Since we are converting date values into columns, we will use <em>[Invoice Date Key]<\/em> here.<br><\/p>\n\n\n\n<p><strong>[Value List]<\/strong>: This is an explicit list of date values to pivot into columns. For this example, it is [3\/8\/2015], [3\/9\/2015], [3\/10\/2015], [3\/11\/2015],\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 [3\/12\/2015], [3\/13\/2015], [3\/14\/2015].<br><\/p>\n\n\n\n<p>When all of the above values are plugged into the <code>PIVOT<\/code> query structure, the result is:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\n\t*\nFROM (\n\tSELECT\n\t\t[Invoice Date Key],\n\t\t[Customer Key],\n\t\tQuantity\n\tFROM Fact.Sale\n\tWHERE [Invoice Date Key] &gt;= '3\/8\/2015'\n\tAND [Invoice Date Key] &lt;= '3\/14\/2015') AS SourceData\nPIVOT (\n\tSUM(Quantity)\n\tFOR [Invoice Date Key] IN ([3\/8\/2015], [3\/9\/2015], [3\/10\/2015], [3\/11\/2015], [3\/12\/2015], [3\/13\/2015], [3\/14\/2015])) AS PivotData;<\/pre><\/div>\n\n\n\n<p>Note that the square brackets are a required part of this syntax. Using quotes or another <a href=\"https:\/\/superops.com\/tech-hub\/what-is-delimiter\" target=\"_blank\" rel=\"noreferrer noopener\">delimiter<\/a> will result in a highly ambiguous error message that looks like this:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"853\" height=\"142\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-29.png\" alt=\"The result of using quotes or another delimiter: this highly ambiguous error message.\" class=\"wp-image-110560\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-29.png 853w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-29-300x50.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-29-768x128.png 768w\" sizes=\"auto, (max-width: 853px) 100vw, 853px\" \/><\/figure>\n\n\n\n<p><em>Yeah, that is quite unhelpful, so be sure to use square brackets around each value in the list!<\/em><\/p>\n\n\n\n<div id=\"callout-block_e955b74a284ef70942e11b1e47f0cf15\" class=\"callout alignnone\">\n    <div class=\"child-last:mb-0 child-first:mt-0 bg-gray-50 dark:bg-gray-950 p-4xl my-3xl\">\n\n<p><strong>Other PIVOT content you may be interested in on Simple Talk<\/strong>&#8230;<\/p>\n\n\n\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/blogs\/pivot-rotation-matrix-transpose-sql-server-new-method\/\" target=\"_blank\" rel=\"noreferrer noopener\">Pivot Rotation and Matrix Transpose in SQL Server: A New Method?<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/easier-way-pivoting-data-sql-server\/\" target=\"_blank\" rel=\"noreferrer noopener\">Pivoting Data in SQL Server with JSON: A Generic Dynamic Matrix Approach<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/questions-about-pivoting-data-in-sql-server-you-were-too-shy-to-ask\/\" target=\"_blank\" rel=\"noreferrer noopener\">SQL Server PIVOT: 12 Questions Answered (Multi-Column, Dynamic, UNPIVOT)<\/a><\/p>\n\n<\/div>\n<\/div> \n\n\n<p>Square brackets are expected because these identifiers are both the values to filter for, <em>and<\/em> the column names that will be used in the result set.<\/p>\n\n\n\n<p>The results of the pivot query are as follows:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"901\" height=\"364\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-30.png\" alt=\"The results of the pivot query.\" class=\"wp-image-110561\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-30.png 901w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-30-300x121.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-30-768x310.png 768w\" sizes=\"auto, (max-width: 901px) 100vw, 901px\" \/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"h-what-about-a-date-with-no-sales\">What about a date with no sales?<\/h4>\n\n\n\n<p>For a date with no sales, the behavior will mimic how the aggregate function would behave if no source values are found. A <code>SUM<\/code> across no rows returns <code>NULL<\/code> &#8211; therefore the example here shows <code>NULL<\/code> when there are no sales on a given date for a customer.<\/p>\n\n\n\n<p>If zeroes are preferable, then the initial <code>SELECT<\/code> can be adjusted from * to a modified column list:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\n\t[Customer Key],\n\tISNULL([3\/8\/2015], 0) AS [3\/8\/2015],\n\tISNULL([3\/9\/2015], 0) AS [3\/9\/2015],\n\tISNULL([3\/10\/2015], 0) AS [3\/10\/2015],\n\tISNULL([3\/11\/2015], 0) AS [3\/11\/2015],\n\tISNULL([3\/12\/2015], 0) AS [3\/12\/2015],\n\tISNULL([3\/13\/2015], 0) AS [3\/13\/2015],\n\tISNULL([3\/14\/2015], 0) AS [3\/14\/2015]\nFROM (\n\tSELECT\n\t\t[Invoice Date Key],\n\t\t[Customer Key],\n\t\tQuantity\n\tFROM Fact.Sale\n\tWHERE [Invoice Date Key] &gt;= '3\/8\/2015'\n\tAND [Invoice Date Key] &lt;= '3\/14\/2015') AS SourceData\nPIVOT (\n\tSUM(Quantity)\n\tFOR [Invoice Date Key] IN ([3\/8\/2015], [3\/9\/2015], [3\/10\/2015], [3\/11\/2015],\n\t[3\/12\/2015], [3\/13\/2015], [3\/14\/2015])) AS PivotData<\/pre><\/div>\n\n\n\n<p>The results are cleaner and show zeroes instead of <code>NULL<\/code>:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"904\" height=\"372\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-31.png\" alt=\"\" class=\"wp-image-110562\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-31.png 904w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-31-300x123.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-31-768x316.png 768w\" sizes=\"auto, (max-width: 904px) 100vw, 904px\" \/><\/figure>\n\n\n\n<div id=\"callout-block_e955b74a284ef70942e11b1e47f0cf15\" class=\"callout alignnone\">\n    <div class=\"child-last:mb-0 child-first:mt-0 bg-gray-50 dark:bg-gray-950 p-4xl my-3xl\">\n\n<p><strong>More reading on <code>NULL<\/code> and <code>SELECT<\/code> you may be interested in&#8230;<\/strong><\/p>\n\n\n\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/the-basic-t-sql-select-statement\/\" target=\"_blank\" rel=\"noreferrer noopener\">The basic T-SQL SELECT statement<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/empty-thoughts-working-with-null\/\" target=\"_blank\" rel=\"noreferrer noopener\">Empty Thoughts: Working with NULL<\/a><\/p>\n\n<\/div>\n<\/div> \n\n\n<p>Multiple columns can be pivoted at once, if needed. For example, if quantity needs to be summed up (as shown above), and [Bill To Customer Key] also included alongside it, then this can be accomplished using two consecutive pivots:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">WITH QuantityData AS (\n\tSELECT\n\t\t[Invoice Date Key],\n\t\t[Customer Key],\n\t\tQuantity\n\tFROM Fact.Sale\n\tWHERE [Invoice Date Key] &gt;= '3\/8\/2015'\n\tAND [Invoice Date Key] &lt;= '3\/14\/2015'),\nBillToCustomerData AS (\n\tSELECT\n\t\t[Customer Key],\n\t\tQuantity,\n\t\t[Bill To Customer Key]\n\tFROM Fact.Sale\n\tWHERE [Invoice Date Key] &gt;= '3\/8\/2015'\n\tAND [Invoice Date Key] &lt;= '3\/14\/2015'),\nPivotDataQuantityByDate AS (\n\tSELECT\n\t\tPivotDataQuantity.[Customer Key],\n\t\tISNULL(PivotDataQuantity.[3\/8\/2015], 0) AS [3\/8 Quantity],\n\t\tISNULL(PivotDataQuantity.[3\/9\/2015], 0) AS [3\/9 Quantity],\n\t\tISNULL(PivotDataQuantity.[3\/10\/2015], 0) AS [3\/10 Quantity],\n\t\tISNULL(PivotDataQuantity.[3\/11\/2015], 0) AS [3\/11 Quantity],\n\t\tISNULL(PivotDataQuantity.[3\/12\/2015], 0) AS [3\/12 Quantity],\n\t\tISNULL(PivotDataQuantity.[3\/13\/2015], 0) AS [3\/13 Quantity],\n\t\tISNULL(PivotDataQuantity.[3\/14\/2015], 0) AS [3\/14 Quantity]\n\tFROM QuantityData\n\tPIVOT (\n\t\tSUM(Quantity)\n\t\tFOR [Invoice Date Key] IN ([3\/8\/2015], [3\/9\/2015], [3\/10\/2015], [3\/11\/2015],\n\t\t[3\/12\/2015], [3\/13\/2015], [3\/14\/2015])) AS PivotDataQuantity),\nPivotDataQuantityByBillToCustomer AS (\n\tSELECT\n\t\tPivotDataQuantity.[Customer Key],\n\t\tISNULL(PivotDataQuantity.[0], 0) AS [0],\n\t\tISNULL(PivotDataQuantity.[1], 0) AS [1],\n\t\tISNULL(PivotDataQuantity.[202], 0) AS [202]\n\tFROM BillToCustomerData\n\tPIVOT (\n\t\tSUM(Quantity)\n\t\tFOR [Bill To Customer Key] IN ([0], [1], [202])) AS PivotDataQuantity)\nSELECT\n\tPivotDataQuantityByBillToCustomer.[Customer Key],\n\tPivotDataQuantityByDate.[3\/8 Quantity],\n\tPivotDataQuantityByDate.[3\/9 Quantity],\n\tPivotDataQuantityByDate.[3\/10 Quantity],\n\tPivotDataQuantityByDate.[3\/11 Quantity],\n\tPivotDataQuantityByDate.[3\/12 Quantity],\n\tPivotDataQuantityByDate.[3\/13 Quantity],\n\tPivotDataQuantityByDate.[3\/14 Quantity],\n\tPivotDataQuantityByBillToCustomer.[0],\n\tPivotDataQuantityByBillToCustomer.[1],\n\tPivotDataQuantityByBillToCustomer.[202]\nFROM PivotDataQuantityByBillToCustomer\nINNER JOIN PivotDataQuantityByDate\nON PivotDataQuantityByDate.[Customer Key] = PivotDataQuantityByBillToCustomer.[Customer Key]\nORDER BY PivotDataQuantityByDate.[Customer Key];<\/pre><\/div>\n\n\n\n<p><strong>This <em>is<\/em> an unusual scenario, but a single T-SQL query can contain as many pivots as you need. Realistically, I\u2019d consider separating each into its own data, stage it in a <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/temporary-tables-in-sql-server\/\" target=\"_blank\" rel=\"noreferrer noopener\">temporary table<\/a>, and then join it all together. This would simplify the overall query and improve performance.<\/strong><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-usage-and-examples-of-unpivot-in-t-sql\">Usage and examples of UNPIVOT in T-SQL<\/h2>\n\n\n\n<p><code>UNPIVOT<\/code> operates similarly to <code>PIVOT<\/code>, except that columns are resolved into row-based data. So, essentially, it performs the <em>reverse<\/em> of what&#8217;s demonstrated above.<\/p>\n\n\n\n<p>Consider the structure of the table <em>Fact.Purchase<\/em>:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"941\" height=\"220\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-34.png\" alt=\"An image showing the Fact.Purchase table\" class=\"wp-image-110579\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-34.png 941w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-34-300x70.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-34-768x180.png 768w\" sizes=\"auto, (max-width: 941px) 100vw, 941px\" \/><\/figure>\n\n\n\n<p>Consider a scenario where we want to adjust this data such that each row is broken into multiple rows per Purchase Key, with a separate row for Ordered Outers, Ordered Quantity, and Received Outers. This is a common scenario when there&#8217;s a desire to split data into distinct files, tables, or sets &#8211; and do so with as little code as possible. It also allows for deformalizing analytic data for use in reports, dashboards, or AI.<\/p>\n\n\n\n<p>The following query accomplishes the above task:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\n\t*\nFROM (\n\tSELECT\n\t\t[Purchase Key],\n\t\t[Date Key],\n\t\t[Ordered Outers],\n\t\t[Ordered Quantity],\n\t\t[Received Outers]\n\tFROM Fact.Purchase) PIVOT_DATA\nUNPIVOT (\n\tQuantity FOR ResultType IN ([Ordered Outers], [Ordered Quantity], [Received Outers])\n) AS UNPIVOT_DATA;<\/pre><\/div>\n\n\n\n<p>Note that the syntax is simpler than <code>PIVOT<\/code> in that aggregation is not necessary to generate a result set. Since <code>UNPIVOT<\/code> is a <a href=\"https:\/\/www.red-gate.com\/blog\/one-to-many-relationship\/\" target=\"_blank\" rel=\"noreferrer noopener\">&#8216;one-to-many&#8217; relationship<\/a> between input and output, the query functions naturally as-is.<\/p>\n\n\n\n<p>The results are as follows:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"853\" height=\"422\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-32.png\" alt=\"The results of the query\" class=\"wp-image-110563\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-32.png 853w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-32-300x148.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-32-768x380.png 768w\" sizes=\"auto, (max-width: 853px) 100vw, 853px\" \/><\/figure>\n\n\n\n<p>Note that each row from the input has been converted into three separate rows, one for each derived quantity. The quantity numbers themselves are not different from how they appeared in the underlying data. They are, however, now split into a row-per-value.<\/p>\n\n\n\n<p>If there happened to be <code>NULL<\/code>s for any of the unpivoted values, then they could be omitted in the results with a single added <code>WHERE<\/code> clause:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\n\t*\nFROM (\n\tSELECT\n\t\t[Purchase Key],\n\t\t[Date Key],\n\t\t[Ordered Outers],\n\t\t[Ordered Quantity],\n\t\t[Received Outers]\n\tFROM Fact.Purchase) PIVOT_DATA\nUNPIVOT (\n\tQuantity FOR ResultType IN ([Ordered Outers], [Ordered Quantity], [Received Outers])\n) AS UNPIVOT_DATA\nWHERE UNPIVOT_DATA.Quantity IS NOT NULL<\/pre><\/div>\n\n\n\n<p>In the above example, all three source quantity columns are <code>NOT NULL<\/code>. <em>If<\/em> any could be <code>NULL<\/code>, then this would ensure that the output does not contain any <code>NULL<\/code>s for Quantity. <\/p>\n\n\n\n<p><strong>In this regard, <code>UNPIVOT<\/code> can be a useful way to remove unknowns from a result set when <code>NULL<\/code> is deemed problematic.<\/strong><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-the-unknown-column-list\">The &#8216;unknown&#8217; column list<\/h2>\n\n\n\n<p><strong>When <code>PIVOT<\/code> operators are used, a new challenge is presented: <em>an explicit column list must be provided<\/em>.<\/strong> <strong>For scenarios where that value list is short, static, and unchanging, this is not an issue. <\/strong><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-however-what-if-values-are-added-or-removed-periodically\">However &#8211; what if values are added or removed periodically? <\/h3>\n\n\n\n<p>Without updating the hard-coded list of values in the <code>PIVOT<\/code> query, the result set will be missing values. There&#8217;s no way to replace the column list with a * or other wildcard character.<\/p>\n\n\n\n<p>This is a scenario where <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/writing-better-dynamic-sql\/\" target=\"_blank\" rel=\"noreferrer noopener\">dynamic SQL<\/a> can rescue us from a whole lot of head-scratching. The simplest solution using dynamic SQL would use the following steps:<\/p>\n\n\n<div class=\"block-core-list\">\n<ol class=\"wp-block-list\">\n<li>Create a list of columns to <code>PIVOT<\/code> on, and store them in a table or <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/find-and-replace-text-in-strings-in-t-sql\/\" target=\"_blank\" rel=\"noreferrer noopener\">string<\/a>.<br><br><\/li>\n\n\n\n<li>Generate dynamic SQL with the initial static query components.<br><br><\/li>\n\n\n\n<li>Insert the dynamically-generated column list into the query.<br><br><\/li>\n\n\n\n<li>Add in the remaining static query components.<\/li>\n<\/ol>\n<\/div>\n\n\n<p>Note that the T-SQL that is ultimately executed will look very similar to the <code>PIVOT<\/code> queries from above, despite the added complexity:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">CREATE TABLE #Dates\n\t([Invoice Date Key] DATE NOT NULL PRIMARY KEY CLUSTERED);\n\nINSERT INTO #Dates\n\t([Invoice Date Key])\nVALUES\n\t('3\/8\/2015'),\n\t('3\/10\/2015'),\n\t('3\/13\/2015'),\n\t('3\/17\/2015'),\n\t('3\/25\/2015');\n\nDECLARE @SqlCommand NVARCHAR(MAX);\nSELECT @SqlCommand = 'SELECT\n\t*\nFROM (\n\tSELECT\n\t\t[Invoice Date Key],\n\t\t[Customer Key],\n\t\tQuantity\n\tFROM Fact.Sale\n\tWHERE Sale.[Invoice Date Key] IN (';\n\nSELECT @SqlCommand = @SqlCommand + '''' + CAST([Invoice Date Key] AS VARCHAR(MAX)) + ''','\nFROM #Dates;\nSELECT @SqlCommand = LEFT(@SqlCommand, LEN(@SqlCommand) - 1);\n\nSELECT @SqlCommand = @SqlCommand + ')) AS SourceData\nPIVOT (\n\tSUM(Quantity)\n\tFOR [Invoice Date Key] IN (';\n\t\nSELECT @SqlCommand = @SqlCommand + '[' + CAST([Invoice Date Key] AS VARCHAR(MAX)) + '],'\nFROM #Dates;\nSELECT @SqlCommand = LEFT(@SqlCommand, LEN(@SqlCommand) - 1);\n\nSELECT @SqlCommand = @SqlCommand + ')) AS PivotData;';\n\nPRINT @SqlCommand\nEXEC sp_executesql @SqlCommand;\n\nDROP TABLE #Dates;<\/pre><\/div>\n\n\n\n<p>At first glance, this T-SQL looks very complex. To put it in perspective, though, consider what the <code>PRINT<\/code> returns as the script to be executed:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\n\t*\nFROM (\n\tSELECT\n\t\t[Invoice Date Key],\n\t\t[Customer Key],\n\t\tQuantity\n\tFROM Fact.Sale\n\tWHERE Sale.[Invoice Date Key] IN ('2015-03-08','2015-03-10','2015-03-13','2015-03-17','2015-03-25')) AS SourceData\nPIVOT (\n\tSUM(Quantity)\n\tFOR [Invoice Date Key] IN ([2015-03-08],[2015-03-10],[2015-03-13],[2015-03-17],[2015-03-25])) AS PivotData;<\/pre><\/div>\n\n\n\n<p>While its generation was complex, the resulting query was no different than <code>PIVOT<\/code> queries from earlier, aside from the data list having been customized.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"h-the-pivot-column-list-can-also-be-generated-using-a-query-rather-than-an-explicit-list\">The <code>PIVOT<\/code> column list can also be generated using a query rather than an explicit list:<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">INSERT INTO #Dates\n\t([Invoice Date Key])\nSELECT DISTINCT\n\t[Invoice Date Key]\nFROM Fact.Sale;\n<\/pre><\/div>\n\n\n\n<p>The absurdly wide result set will look like this:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"938\" height=\"155\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-33.png\" alt=\"An image showing the absurdly wide result set.\" class=\"wp-image-110564\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-33.png 938w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-33-300x50.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/05\/image-33-768x127.png 768w\" sizes=\"auto, (max-width: 938px) 100vw, 938px\" \/><\/figure>\n\n\n\n<p><strong>Generally speaking, a data set with 1,000+ columns is a very bad idea \ud83d\ude0a<\/strong><\/p>\n\n\n\n<p><strong>When generating dynamic <code>PIVOT<\/code> data, it&#8217;s important to ensure that the column list cannot grow unbounded. A <code>WHERE<\/code> clause on the <code>INSERT<\/code> into the temporary table would accomplish that easily enough:<\/strong><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">INSERT INTO #Dates\n\t([Invoice Date Key])\nSELECT DISTINCT\n\t[Invoice Date Key]\nFROM Fact.Sale\nWHERE [Invoice Date Key] &gt;= '5\/1\/2016'\nAND [Invoice Date Key] &lt; '6\/1\/2016';<\/pre><\/div>\n\n\n\n<p><strong>That adjustment ensures that the pivoted column list contains only data from May 2016. It will, therefore, only output data that produces no more than 31 possible columns.<\/strong> <\/p>\n\n\n\n<p><em>As an aside, the unbounded query that generated 1000+ columns did crash my SQL Server Management Studio. I&#8217;m sure the query optimizer and SQL output generator were not very happy with my life choices! \ud83d\ude0a<\/em><\/p>\n\n\n\n<p>Dynamic <code>UNPIVOT<\/code> is generated in the same fashion as dynamic <code>PIVOT<\/code>, with the added work being to generate a column list that gets inserted into the <code>UNPIVOT<\/code> query. As an alternative to a temporary table, a string that&#8217;s built using the required values &#8211; whatever they happen to be &#8211; could be generated instead.<\/p>\n\n\n\n<p><strong>Whether you choose a table or string depends solely on your T-SQL coding comfort and\/or whatever is an easier method to collect the desired values.<\/strong><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-in-conclusion\">In conclusion&#8230;<\/h2>\n\n\n\n<p><strong>Despite the initial headaches that <code>PIVOT<\/code> and <code>UNPIVOT<\/code> often cause developers and database professionals, there&#8217;s no reason to avoid these operators. They&#8217;re very efficient for columns that need to be resolved into rows, or row-based data flipped into columns.<\/strong><\/p>\n\n\n\n<p>Even if your coding experience has never required the use of transformations like this, simply consider them usable, accessible, and less complex than they may seem at first glance. They may one day be the source of a brilliant data solution for you!<\/p>\n\n\n\n<section id=\"my-first-block-block_531802792f2bf90e5fcdc1f24bc06453\" class=\"my-first-block alignwide\">\n    <div class=\"bg-brand-600 text-base-white py-5xl px-4xl rounded-sm bg-gradient-to-r from-brand-600 to-brand-500 red\">\n        <div class=\"gap-4xl items-start md:items-center flex flex-col md:flex-row justify-between\">\n            <div class=\"flex-1 col-span-10 lg:col-span-7\">\n                <h3 class=\"mt-0 font-display mb-2 text-display-sm\">Simple Talk is brought to you by Redgate Software<\/h3>\n                <div class=\"child:last-of-type:mb-0\">\n                                            Take control of your databases with the trusted Database DevOps solutions provider. Automate with confidence, scale securely, and unlock growth through AI.                                    <\/div>\n            <\/div>\n                                            <a href=\"https:\/\/www.red-gate.com\/solutions\/overview\/\" class=\"btn btn--secondary btn--lg\" aria-label=\"Discover how Redgate can help you: Simple Talk is brought to you by Redgate Software\">Discover how Redgate can help you<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n<section id=\"faq\" class=\"faq-block my-5xl\">\n    <h2>FAQs: PIVOT and UNPIVOT in T-SQL<\/h2>\n\n                        <h3 class=\"mt-4xl\">1. What does PIVOT do in T-SQL?<\/h3>\n            <div class=\"faq-answer\">\n                <div>\n<p>PIVOT converts row-based data into columns, applying an aggregate function (like SUM or COUNT) as it goes. It&#8217;s commonly used in reporting to reformat a result set \u2014 for example, turning one row per date into one column per date.<\/p>\n<\/div>\n            <\/div>\n                    <h3 class=\"mt-4xl\">2. What is the difference between PIVOT and UNPIVOT?<\/h3>\n            <div class=\"faq-answer\">\n                <div>\n<p>PIVOT turns rows into columns; UNPIVOT does the reverse, expanding column values into multiple rows. UNPIVOT doesn&#8217;t require an aggregate function since it&#8217;s a one-to-many operation \u2014 each input row produces multiple output rows.<\/p>\n<\/div>\n            <\/div>\n                    <h3 class=\"mt-4xl\">3. Why do I need an aggregate function in PIVOT even if my data doesn&#039;t need aggregating?<\/h3>\n            <div class=\"faq-answer\">\n                <div>\n<p>PIVOT syntax requires an aggregate function regardless of whether aggregation is meaningful in your context. If no actual aggregation is needed, simply use whichever function is simplest (e.g. MAX or SUM) and add a comment noting that the choice doesn&#8217;t affect the result.<\/p>\n<\/div>\n            <\/div>\n                    <h3 class=\"mt-4xl\">4. Why does T-SQL PIVOT require square brackets around column values?<\/h3>\n            <div class=\"faq-answer\">\n                <div>\n<p>The values in a PIVOT&#8217;s IN list serve double duty \u2014 they&#8217;re both the filter values and the resulting column names. T-SQL treats them as identifiers, which requires square bracket notation. Using quotes instead produces a cryptic error message that gives little indication of the actual problem.<\/p>\n<\/div>\n            <\/div>\n                    <h3 class=\"mt-4xl\">5. How do I handle NULL values in PIVOT output?<\/h3>\n            <div class=\"faq-answer\">\n                <div>\n<p>By default, PIVOT returns NULL when no source rows match a given column value. To replace NULLs with zeroes (or another default), replace the\u00a0<code>SELECT *<\/code>\u00a0in the outer query with an explicit column list that wraps each pivoted column in\u00a0<code>ISNULL()<\/code>.<\/p>\n<\/div>\n            <\/div>\n                    <h3 class=\"mt-4xl\">6. What is dynamic PIVOT in SQL Server, and when do you need it?<\/h3>\n            <div class=\"faq-answer\">\n                <div>\n<p>Standard PIVOT requires a hard-coded column list \u2014 if new values appear in your data, they won&#8217;t show up in results unless you update the query. Dynamic PIVOT uses dynamic SQL to build that column list at runtime from the actual data, making the query self-updating. It&#8217;s the right approach when the set of values changes periodically.<\/p>\n<\/div>\n            <\/div>\n                    <h3 class=\"mt-4xl\">7. Can you PIVOT on multiple columns at once?<\/h3>\n            <div class=\"faq-answer\">\n                <div>\n<p>Yes, but T-SQL doesn&#8217;t support multi-column PIVOT in a single operator. The recommended approach is to run separate PIVOT operations (using CTEs or temporary tables) and then JOIN the results together on a shared key column.<\/p>\n<\/div>\n            <\/div>\n                    <h3 class=\"mt-4xl\">8. Which SQL platforms support PIVOT and UNPIVOT?<\/h3>\n            <div class=\"faq-answer\">\n                <div id=\"vis-container\" class=\"\">\n<div>\n<div>\n<div>\n<div>\n<p>PIVOT and UNPIVOT are T-SQL features, not part of the ANSI SQL standard. They&#8217;re available in SQL Server, Azure SQL Database, Azure Synapse Analytics, and SQL Database in Microsoft Fabric \u2014 but not in MySQL, PostgreSQL, or most other SQL dialects.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n            <\/div>\n            <\/section>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Learn how to use T-SQL PIVOT and UNPIVOT operators with clear examples \u2014 from basic row-to-column transforms to dynamic SQL solutions for unknown column lists.<br \/>\n&hellip;<\/p>\n","protected":false},"author":329827,"featured_media":110581,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[137091,10,143523,53,143525,143524,143531],"tags":[4168,4170,4150,4151,4183],"coauthors":[101655],"class_list":["post-110557","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure","category-cloud","category-databases","category-featured","category-learn","category-sql-server","category-t-sql-programming-sql-server","tag-database","tag-database-administration","tag-sql","tag-sql-server","tag-t-sql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/110557","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\/329827"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=110557"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/110557\/revisions"}],"predecessor-version":[{"id":110585,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/110557\/revisions\/110585"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/110581"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=110557"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=110557"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=110557"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=110557"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}