{"id":21257,"date":"2024-03-15T21:44:24","date_gmt":"2024-03-15T21:44:24","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/what-are-the-different-ways-to-pivot-columns-in-sql\/"},"modified":"2024-03-21T20:03:17","modified_gmt":"2024-03-21T20:03:17","slug":"what-are-the-different-ways-to-pivot-columns-in-sql","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/what-are-the-different-ways-to-pivot-columns-in-sql\/","title":{"rendered":"What are the different ways to pivot columns in SQL?"},"content":{"rendered":"

There are several methods in SQL for pivoting columns into rows.<\/p>\n

    \n
  1. By using the UNPIVOT operator, columns can be transformed into rows. It requires specifying the columns to be converted and the column names for the generated rows. For example:<\/li>\n<\/ol>\n
    SELECT customer_id, product, quantity\r\nFROM sales\r\nUNPIVOT\r\n(\r\n  quantity\r\n  FOR product IN (product_A, product_B, product_C)\r\n) AS unpvt;\r\n<\/code><\/pre>\n
      \n
    1. Using CROSS APPLY: The CROSS APPLY operator can pivot columns into rows. It can be used together with a VALUES clause to convert column values into rows. For example:<\/li>\n<\/ol>\n
      SELECT s.customer_id, v.product, v.quantity\r\nFROM sales s\r\nCROSS APPLY \r\n(\r\n  VALUES \r\n  ('product_A', s.product_A),\r\n  ('product_B', s.product_B),\r\n  ('product_C', s.product_C)\r\n) AS v (product, quantity);\r\n<\/code><\/pre>\n
        \n
      1. Using UNION ALL: The UNION ALL operator can combine the result sets of multiple queries into one result set. When using UNION ALL, each query should return the same number of columns and the same data types. For example:<\/li>\n<\/ol>\n
        SELECT customer_id, 'product_A' AS product, product_A AS quantity\r\nFROM sales\r\nUNION ALL\r\nSELECT customer_id, 'product_B' AS product, product_B AS quantity\r\nFROM sales\r\nUNION ALL\r\nSELECT customer_id, 'product_C' AS product, product_C AS quantity\r\nFROM sales;\r\n<\/code><\/pre>\n
        There are common methods for converting columns to rows, the specific method used depends on the database support and the requirements of the query.<\/p>\n","protected":false},"excerpt":{"rendered":"
        There are several methods in SQL for pivoting columns into rows. By using the UNPIVOT operator, columns can be transformed into rows. It requires specifying the columns to be converted and the column names for the generated rows. For example: SELECT customer_id, product, quantity FROM sales UNPIVOT ( quantity FOR product IN (product_A, product_B, product_C) […]<\/p>\n","protected":false},"author":5,"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-21257","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"\nWhat are the different ways to pivot columns in SQL? - 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\/what-are-the-different-ways-to-pivot-columns-in-sql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"What are the different ways to pivot columns in SQL?\" \/>\n<meta property=\"og:description\" content=\"There are several methods in SQL for pivoting columns into rows. By using the UNPIVOT operator, columns can be transformed into rows. It requires specifying the columns to be converted and the column names for the generated rows. For example: SELECT customer_id, product, quantity FROM sales UNPIVOT ( quantity FOR product IN (product_A, product_B, product_C) […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/what-are-the-different-ways-to-pivot-columns-in-sql\/\" \/>\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-15T21:44:24+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-21T20:03:17+00:00\" \/>\n<meta name=\"author\" content=\"Emily Johnson\" \/>\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=\"Emily Johnson\" \/>\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\/what-are-the-different-ways-to-pivot-columns-in-sql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/what-are-the-different-ways-to-pivot-columns-in-sql\/\"},\"author\":{\"name\":\"Emily Johnson\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/3b041b19cffc258705478ecfab895378\"},\"headline\":\"What are the different ways to pivot columns in SQL?\",\"datePublished\":\"2024-03-15T21:44:24+00:00\",\"dateModified\":\"2024-03-21T20:03:17+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/what-are-the-different-ways-to-pivot-columns-in-sql\/\"},\"wordCount\":142,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/what-are-the-different-ways-to-pivot-columns-in-sql\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/what-are-the-different-ways-to-pivot-columns-in-sql\/\",\"name\":\"What are the different ways to pivot columns in SQL? - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-15T21:44:24+00:00\",\"dateModified\":\"2024-03-21T20:03:17+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/what-are-the-different-ways-to-pivot-columns-in-sql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/what-are-the-different-ways-to-pivot-columns-in-sql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/what-are-the-different-ways-to-pivot-columns-in-sql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"What are the different ways to pivot columns in SQL?\"}]},{\"@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\/3b041b19cffc258705478ecfab895378\",\"name\":\"Emily Johnson\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/a5cb4e73d02ab1d79f2dfe919389ff7c1de072baa97686392031c03d858cc358?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/a5cb4e73d02ab1d79f2dfe919389ff7c1de072baa97686392031c03d858cc358?s=96&d=mm&r=g\",\"caption\":\"Emily Johnson\"},\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/emilyjohnson\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"What are the different ways to pivot columns in SQL? - 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\/what-are-the-different-ways-to-pivot-columns-in-sql\/","og_locale":"en_US","og_type":"article","og_title":"What are the different ways to pivot columns in SQL?","og_description":"There are several methods in SQL for pivoting columns into rows. By using the UNPIVOT operator, columns can be transformed into rows. It requires specifying the columns to be converted and the column names for the generated rows. For example: SELECT customer_id, product, quantity FROM sales UNPIVOT ( quantity FOR product IN (product_A, product_B, product_C) […]","og_url":"https:\/\/www.silicloud.com\/blog\/what-are-the-different-ways-to-pivot-columns-in-sql\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-15T21:44:24+00:00","article_modified_time":"2024-03-21T20:03:17+00:00","author":"Emily Johnson","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"Emily Johnson","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/what-are-the-different-ways-to-pivot-columns-in-sql\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/what-are-the-different-ways-to-pivot-columns-in-sql\/"},"author":{"name":"Emily Johnson","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/3b041b19cffc258705478ecfab895378"},"headline":"What are the different ways to pivot columns in SQL?","datePublished":"2024-03-15T21:44:24+00:00","dateModified":"2024-03-21T20:03:17+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/what-are-the-different-ways-to-pivot-columns-in-sql\/"},"wordCount":142,"commentCount":0,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/what-are-the-different-ways-to-pivot-columns-in-sql\/","url":"https:\/\/www.silicloud.com\/blog\/what-are-the-different-ways-to-pivot-columns-in-sql\/","name":"What are the different ways to pivot columns in SQL? - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-15T21:44:24+00:00","dateModified":"2024-03-21T20:03:17+00:00","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/what-are-the-different-ways-to-pivot-columns-in-sql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/what-are-the-different-ways-to-pivot-columns-in-sql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/what-are-the-different-ways-to-pivot-columns-in-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"What are the different ways to pivot columns in SQL?"}]},{"@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\/3b041b19cffc258705478ecfab895378","name":"Emily Johnson","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/a5cb4e73d02ab1d79f2dfe919389ff7c1de072baa97686392031c03d858cc358?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a5cb4e73d02ab1d79f2dfe919389ff7c1de072baa97686392031c03d858cc358?s=96&d=mm&r=g","caption":"Emily Johnson"},"url":"https:\/\/www.silicloud.com\/blog\/author\/emilyjohnson\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/21257","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\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=21257"}],"version-history":[{"count":1,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/21257\/revisions"}],"predecessor-version":[{"id":55108,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/21257\/revisions\/55108"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=21257"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=21257"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=21257"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}