{"id":17577,"date":"2024-03-15T15:16:36","date_gmt":"2024-03-15T15:16:36","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/how-to-pivot-rows-into-columns-in-sql-server\/"},"modified":"2024-03-21T11:14:21","modified_gmt":"2024-03-21T11:14:21","slug":"how-to-pivot-rows-into-columns-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/how-to-pivot-rows-into-columns-in-sql-server\/","title":{"rendered":"How to pivot rows into columns in SQL Server?"},"content":{"rendered":"

In SQL Server, you can use the PIVOT operation to transform rows into columns. PIVOT operation allows you to use unique values as column headers and populate the new columns with values from the rows.<\/p>\n

Here are some examples of using the PIVOT operation:<\/p>\n

    \n
  1. Basic PIVOT operation:<\/li>\n<\/ol>\n
    SELECT *\r\nFROM (\r\n   SELECT column1, column2, column3\r\n   FROM table_name\r\n) AS SourceTable\r\nPIVOT (\r\n   MAX(column3) -- \u9009\u62e9\u4e00\u4e2a\u805a\u5408\u51fd\u6570\uff0c\u4f8b\u5982SUM\u3001COUNT\u3001MAX\u7b49\r\n   FOR column1 -- \u5217\u6807\u9898\u6240\u5728\u7684\u5217\r\n   IN (value1, value2, value3) -- \u5217\u6807\u9898\u7684\u552f\u4e00\u503c\u5217\u8868\r\n) AS PivotTable;\r\n<\/code><\/pre>\n
      \n
    1. Performing a PIVOT operation with dynamic column headers.<\/li>\n<\/ol>\n
      DECLARE @columnList AS NVARCHAR(MAX);\r\nDECLARE @pivotQuery AS NVARCHAR(MAX);\r\n\r\nSELECT @columnList = CONCAT(@columnList, ', ', QUOTENAME(column1))\r\nFROM (\r\n   SELECT DISTINCT column1\r\n   FROM table_name\r\n) AS ColumnListTable;\r\n\r\nSET @pivotQuery = CONCAT('\r\n   SELECT *\r\n   FROM (\r\n      SELECT column1, column2, column3\r\n      FROM table_name\r\n   ) AS SourceTable\r\n   PIVOT (\r\n      MAX(column3)\r\n      FOR column1\r\n      IN (', @columnList, ')\r\n   ) AS PivotTable;\r\n');\r\n\r\nEXECUTE sp_executesql @pivotQuery;\r\n<\/code><\/pre>\n
      In the example above, table_name refers to the name of the original table being converted, column1 is the name of the column containing the column headers, and column2 and column3 are the names of the columns containing the values to be converted. value1, value2, and value3 are unique value lists for the column headers.<\/p>\n
      It is important to note that the PIVOT operation is specific to SQL Server and may not be applicable to all database management systems. If you are using a different database, you may need to utilize a different method to achieve the transformation from rows to columns.<\/p>\n","protected":false},"excerpt":{"rendered":"
      In SQL Server, you can use the PIVOT operation to transform rows into columns. PIVOT operation allows you to use unique values as column headers and populate the new columns with values from the rows. Here are some examples of using the PIVOT operation: Basic PIVOT operation: SELECT * FROM ( SELECT column1, column2, column3 […]<\/p>\n","protected":false},"author":9,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","footnotes":""},"categories":[1],"tags":[],"class_list":["post-17577","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"\nHow to pivot rows into columns in SQL Server? - Blog - Silicon Cloud<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.silicloud.com\/blog\/how-to-pivot-rows-into-columns-in-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to pivot rows into columns in SQL Server?\" \/>\n<meta property=\"og:description\" content=\"In SQL Server, you can use the PIVOT operation to transform rows into columns. PIVOT operation allows you to use unique values as column headers and populate the new columns with values from the rows. Here are some examples of using the PIVOT operation: Basic PIVOT operation: SELECT * FROM ( SELECT column1, column2, column3 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/how-to-pivot-rows-into-columns-in-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog - Silicon Cloud\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-15T15:16:36+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-21T11:14:21+00:00\" \/>\n<meta name=\"author\" content=\"Ava Mitchell\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:site\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Ava Mitchell\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-pivot-rows-into-columns-in-sql-server\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-pivot-rows-into-columns-in-sql-server\/\"},\"author\":{\"name\":\"Ava Mitchell\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/a3e2658c2cb9fb2be95ae0a8861f4a64\"},\"headline\":\"How to pivot rows into columns in SQL Server?\",\"datePublished\":\"2024-03-15T15:16:36+00:00\",\"dateModified\":\"2024-03-21T11:14:21+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-pivot-rows-into-columns-in-sql-server\/\"},\"wordCount\":166,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-pivot-rows-into-columns-in-sql-server\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/how-to-pivot-rows-into-columns-in-sql-server\/\",\"name\":\"How to pivot rows into columns in SQL Server? - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-15T15:16:36+00:00\",\"dateModified\":\"2024-03-21T11:14:21+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-pivot-rows-into-columns-in-sql-server\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/how-to-pivot-rows-into-columns-in-sql-server\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-pivot-rows-into-columns-in-sql-server\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to pivot rows into columns in SQL Server?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"name\":\"Silicon Cloud Blog\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\",\"name\":\"Silicon Cloud Blog\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"contentUrl\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"width\":1024,\"height\":1024,\"caption\":\"Silicon Cloud Blog\"},\"image\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\",\"https:\/\/twitter.com\/SiliCloudGlobal\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/a3e2658c2cb9fb2be95ae0a8861f4a64\",\"name\":\"Ava Mitchell\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/15c63cd0564b4a2e07d611bcdffa296f6ea80e8db07c3091f43a84010514899d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/15c63cd0564b4a2e07d611bcdffa296f6ea80e8db07c3091f43a84010514899d?s=96&d=mm&r=g\",\"caption\":\"Ava Mitchell\"},\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/avamitchell\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How to pivot rows into columns in SQL Server? - Blog - Silicon Cloud","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.silicloud.com\/blog\/how-to-pivot-rows-into-columns-in-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"How to pivot rows into columns in SQL Server?","og_description":"In SQL Server, you can use the PIVOT operation to transform rows into columns. PIVOT operation allows you to use unique values as column headers and populate the new columns with values from the rows. Here are some examples of using the PIVOT operation: Basic PIVOT operation: SELECT * FROM ( SELECT column1, column2, column3 […]","og_url":"https:\/\/www.silicloud.com\/blog\/how-to-pivot-rows-into-columns-in-sql-server\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-15T15:16:36+00:00","article_modified_time":"2024-03-21T11:14:21+00:00","author":"Ava Mitchell","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"Ava Mitchell","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/how-to-pivot-rows-into-columns-in-sql-server\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-pivot-rows-into-columns-in-sql-server\/"},"author":{"name":"Ava Mitchell","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/a3e2658c2cb9fb2be95ae0a8861f4a64"},"headline":"How to pivot rows into columns in SQL Server?","datePublished":"2024-03-15T15:16:36+00:00","dateModified":"2024-03-21T11:14:21+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-pivot-rows-into-columns-in-sql-server\/"},"wordCount":166,"commentCount":0,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/how-to-pivot-rows-into-columns-in-sql-server\/","url":"https:\/\/www.silicloud.com\/blog\/how-to-pivot-rows-into-columns-in-sql-server\/","name":"How to pivot rows into columns in SQL Server? - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-15T15:16:36+00:00","dateModified":"2024-03-21T11:14:21+00:00","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-pivot-rows-into-columns-in-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/how-to-pivot-rows-into-columns-in-sql-server\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/how-to-pivot-rows-into-columns-in-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to pivot rows into columns in SQL Server?"}]},{"@type":"WebSite","@id":"https:\/\/www.silicloud.com\/blog\/#website","url":"https:\/\/www.silicloud.com\/blog\/","name":"Silicon Cloud Blog","description":"","publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.silicloud.com\/blog\/#organization","name":"Silicon Cloud Blog","url":"https:\/\/www.silicloud.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","contentUrl":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","width":1024,"height":1024,"caption":"Silicon Cloud Blog"},"image":{"@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/SiliCloudGlobal\/","https:\/\/twitter.com\/SiliCloudGlobal"]},{"@type":"Person","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/a3e2658c2cb9fb2be95ae0a8861f4a64","name":"Ava Mitchell","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/15c63cd0564b4a2e07d611bcdffa296f6ea80e8db07c3091f43a84010514899d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/15c63cd0564b4a2e07d611bcdffa296f6ea80e8db07c3091f43a84010514899d?s=96&d=mm&r=g","caption":"Ava Mitchell"},"url":"https:\/\/www.silicloud.com\/blog\/author\/avamitchell\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/17577","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/users\/9"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=17577"}],"version-history":[{"count":1,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/17577\/revisions"}],"predecessor-version":[{"id":51198,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/17577\/revisions\/51198"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=17577"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=17577"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=17577"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}