{"id":7598,"date":"2024-02-26T05:28:00","date_gmt":"2024-02-26T04:28:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=7598"},"modified":"2026-05-25T18:35:47","modified_gmt":"2026-05-25T16:35:47","slug":"how-to-pivot-rows-to-columns-in-sql-server","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/t-sql\/how-to-pivot-rows-to-columns-in-sql-server\/","title":{"rendered":"Pivot Rows To Columns in SQL Server: Easy Query in 2 Steps"},"content":{"rendered":"\n<p class=\"has-text-align-center\"><em><strong>How to transform data from rows to columns in a SQL Server query?<\/strong><\/em><\/p>\n\n\n\n<p>Follow this short and step by step tutorial to simply transform data and Pivot rows to columns in SQL Server. In other words, using the T-SQL PIVOT operator to convert lines of data into named columns.<\/p>\n\n\n\n<p>Note that the syntax is not super straightforward, especially for beginners. Indeed, in order to work, the names of the target columns must be provided explicitly. And they must match the content of the pivoted column.<\/p>\n\n\n\n<p>This simple Pivot query example shows how to build a first one and adapt it of course to your own needs, step by step. It simply moves the lines containing the months names to columns while computing the average amount of sales for each given month.<\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_84 ez-toc-wrap-center counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-pivot-rows-to-columns-in-sql-server\/#1-syntax-of-operator-to-pivot-rows-to-columns-in-sql-server\" >1. Syntax of Operator to Pivot Rows to Columns in SQL Server<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-pivot-rows-to-columns-in-sql-server\/#2-create-a-sql-server-table-to-transform-from-rows-to-columns\" >2. Create a SQL Server table to transform from rows to columns<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-pivot-rows-to-columns-in-sql-server\/#3-build-and-run-the-sql-server-pivot-query-with-one-fixed-column\" >3. Build and run the SQL Server PIVOT query with one fixed column<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-pivot-rows-to-columns-in-sql-server\/#about-a-simple-query-to-pivot-rows-to-columns-in-sql-server\" >About a simple query to Pivot Rows to Columns in SQL Server<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\" id=\"h-1-sql-server-pivot-queries-and-aggregations\"><span class=\"ez-toc-section\" id=\"1-syntax-of-operator-to-pivot-rows-to-columns-in-sql-server\"><\/span>1. Syntax of Operator to Pivot Rows to Columns in SQL Server<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>In the other hand, if we don&#8217;t want to have any aggregation in the new results lines, then we need exactly one line per column created. In the example below we do a pivot with an aggregation and we use the average function.<\/p>\n\n\n\n<p>And only the six first months of the year are used and pivoted, namely January to June. It&#8217;s easy to extend to the end of the year by adding the next 6 months. To do so, just copy\/paste the data creation and query, and add the missing months.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-2-create-a-sql-server-table-to-transform-from-rows-to-columns\"><span class=\"ez-toc-section\" id=\"2-create-a-sql-server-table-to-transform-from-rows-to-columns\"><\/span>2. Create a SQL Server table to transform from rows to columns<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Before building the query, create the sample table with this T-SQL script, simply copy and paste it to your SSMS window.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">-- If table exits, drop it\nIF exists( \tSELECT \t1 FROM sys.objects\n            WHERE \tobject_id = object_id(N'[dbo].[SALES]') \n                AND type in (N'U') )\nBEGIN \t\n    DROP TABLE [dbo].[SALES]\nEND\nGO\n\n-- SALES table creation\nCREATE table [dbo].[SALES] (\n    [MONTH] NVARCHAR(20),\n    [AMOUNT] NUMERIC(5)\n)\nGO\n\n-- Insert first sales amount for each month\nINSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'January', 1000)\nINSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'February', 2000)\nINSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'March', 3000)\nINSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'April', 4000)\nINSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'May', 5000)\nINSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'June', 6000)\n\n-- Insert second sales amount for each month\nINSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'January', 1100)\nINSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'February', 2200)\nINSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'March', 3300)\nINSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'April', 4400)\nINSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'May', 5500)\nINSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'June', 6600)\n\n-- Check inserted data\nSELECT \t*\nFROM \tdbo.SALES;\n<\/pre>\n\n\n\n<p>The SQL Server table <a href=\"https:\/\/www.investopedia.com\/terms\/p\/pivot.asp\" target=\"_blank\" rel=\"noreferrer noopener\">to pivot<\/a> is presented here in lines and 2 columns are available. The goal for the next step is to have each month with an average value of the sales.<\/p>\n\n\n<div class=\"wp-block-image wp-image-374 size-full\">\n<figure class=\"aligncenter is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"628\" height=\"284\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2023\/10\/SQL-Server-Insert-Sales-Data-before-pivot.png\" alt=\"SQL table with data in lines before using the pivot operator\" class=\"wp-image-30321\" style=\"width:628px;height:auto\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2023\/10\/SQL-Server-Insert-Sales-Data-before-pivot.png 628w, https:\/\/expert-only.com\/wp-content\/uploads\/2023\/10\/SQL-Server-Insert-Sales-Data-before-pivot-300x136.png 300w\" sizes=\"auto, (max-width: 628px) 100vw, 628px\" \/><figcaption class=\"wp-element-caption\"><em><strong>SQL table with data in lines before using the pivot operator<\/strong><\/em><\/figcaption><\/figure>\n<\/div>\n\n\n<h2 class=\"wp-block-heading\" id=\"h-3-build-and-run-the-sql-server-pivot-query-with-one-fixed-column\"><span class=\"ez-toc-section\" id=\"3-build-and-run-the-sql-server-pivot-query-with-one-fixed-column\"><\/span>3. Build and run the SQL Server PIVOT query with one fixed column<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>The query to pivot rows into columns is compound of these three parts,&nbsp;it computes the average sales per month:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>A selection of the aggregated column and the months<\/strong>, each column called explicitly.<\/li>\n\n\n\n<li><strong>The sub-query with the original selection<\/strong> of data.<\/li>\n\n\n\n<li><strong>The PIVOT itself using the AVG aggregation<\/strong> function.<\/li>\n<\/ol>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">SELECT \t'Average SALES' AS [SALES_PER_MONTH],\n        [January], [February], [March], [April], [May], [June]\nFROM (\n    SELECT [MONTH], [AMOUNT]\n    FROM dbo.SALES\n) AS SourceTable\nPIVOT (\n    AVG(AMOUNT)\n    FOR MONTH IN ([January], [February], [March], [April], [May],[June])\n) AS PivotTable;\n<\/pre>\n\n\n\n<p>The result of the query appears in columns after the query using PIVOT.<\/p>\n\n\n<div class=\"wp-block-image wp-image-375 size-full\">\n<figure class=\"aligncenter\"><img loading=\"lazy\" decoding=\"async\" width=\"722\" height=\"315\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2024\/02\/PIVOT-Query-SQL-Server.png\" alt=\"Example of query to Pivot rows to columns in SQL Server\" class=\"wp-image-30192\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2024\/02\/PIVOT-Query-SQL-Server.png 722w, https:\/\/expert-only.com\/wp-content\/uploads\/2024\/02\/PIVOT-Query-SQL-Server-300x131.png 300w\" sizes=\"auto, (max-width: 722px) 100vw, 722px\" \/><figcaption class=\"wp-element-caption\"><em><strong>Example of query to Pivot rows to columns in SQL Server<\/strong><\/em><\/figcaption><\/figure>\n<\/div>\n\n\n<h2 class=\"wp-block-heading\" id=\"h-about-the-simple-pivot-query-use-case\"><span class=\"ez-toc-section\" id=\"about-a-simple-query-to-pivot-rows-to-columns-in-sql-server\"><\/span>About a simple query to Pivot Rows to Columns in SQL Server<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>To finish, this Transact-SQL tutorial explains how to Pivot Rows to Columns in SQL Server with a step by step example. This example only uses 2 queries.<\/p>\n\n\n\n<p>To go further and query system tables metadata, use the <a href=\"https:\/\/expert-only.com\/en\/t-sql\/modification-date-of-sql-server-tables\/\">MS SQL query to display the date and time of the last modification of a table<\/a>. Of course you can also do the reverse operation to transpose columns into rows using the <a href=\"https:\/\/expert-only.com\/en\/t-sql\/sql-server-unpivot\/\">UNPIVOT operator<\/a>. But also Pivot or Unpivot data manually with Excel&#8230;<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-more-tutorials-about-how-to-pivot-and-unpivot-data\"><em>More tutorials about how to Pivot and Unpivot data<\/em><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/expert-only.com\/en\/t-sql\/sql-server-pivot-with-multiple-fixed-columns\/\">SQL Server PIVOT with Multiple Fixed Columns<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/expert-only.com\/en\/excel\/pivot-excel-table\/\">Tutorial to Pivot Excel table columns into rows.<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/expert-only.com\/en\/excel\/create-excel-pivot-table\/\">How to create an Excel pivot table to analyse data?<\/a><\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-embed aligncenter is-type-wp-embed is-provider-sql-server-and-data-tutorials wp-block-embed-sql-server-and-data-tutorials\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"ng31Jabqf3\"><a href=\"https:\/\/expert-only.com\/en\/t-sql\/sql-server-unpivot\/\">SQL Server UNPIVOT Example<\/a><\/blockquote><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; visibility: hidden;\" title=\"&#8220;SQL Server UNPIVOT Example&#8221; &#8212; SQL Server and Data Tutorials\" src=\"https:\/\/expert-only.com\/en\/t-sql\/sql-server-unpivot\/embed\/#?secret=5RNdfKG58W#?secret=ng31Jabqf3\" data-secret=\"ng31Jabqf3\" width=\"500\" height=\"282\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n","protected":false},"excerpt":{"rendered":"<div class=\"mh-excerpt\"><p>How to transform data from rows to columns in a SQL Server query? Follow this short and step by step tutorial to simply transform data and Pivot rows to columns in SQL Server. In other words, using the T-SQL <a class=\"mh-excerpt-more\" href=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-pivot-rows-to-columns-in-sql-server\/\" title=\"Pivot Rows To Columns in SQL Server: Easy Query in 2 Steps\">&#8230;<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":5735,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[454],"tags":[],"class_list":{"0":"post-7598","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-t-sql"},"_links":{"self":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/7598","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/comments?post=7598"}],"version-history":[{"count":33,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/7598\/revisions"}],"predecessor-version":[{"id":31312,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/7598\/revisions\/31312"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media\/5735"}],"wp:attachment":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media?parent=7598"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/categories?post=7598"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/tags?post=7598"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}