{"id":17571,"date":"2024-03-15T15:13:47","date_gmt":"2024-03-15T15:13:47","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/how-to-implement-converting-multiple-rows-to-multiple-columns-in-sql-pivot\/"},"modified":"2024-03-21T11:13:28","modified_gmt":"2024-03-21T11:13:28","slug":"how-to-implement-converting-multiple-rows-to-multiple-columns-in-sql-pivot","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/how-to-implement-converting-multiple-rows-to-multiple-columns-in-sql-pivot\/","title":{"rendered":"How to implement converting multiple rows to multiple columns in SQL pivot?"},"content":{"rendered":"

In SQL, you can use the CASE WHEN statement in conjunction with aggregate functions to pivot multiple rows of data into multiple columns. The specific steps are as follows:<\/p>\n

    \n
  1. Organizing by category<\/li>\n
  2. IF<\/li>\n
  3. Aggregate functions are applied to the transposed columns for aggregation.<\/li>\n<\/ol>\n

    Here is an example: consider a table called Orders, which includes columns for OrderID, ProductName, and Quantity.<\/p>\n

    SELECT OrderID,\r\n       MAX(CASE WHEN ProductName = 'Product A' THEN Quantity END) AS ProductA,\r\n       MAX(CASE WHEN ProductName = 'Product B' THEN Quantity END) AS ProductB,\r\n       MAX(CASE WHEN ProductName = 'Product C' THEN Quantity END) AS ProductC\r\nFROM Orders\r\nGROUP BY OrderID;\r\n<\/code><\/pre>\n
    The above query will pivot the quantity of each product in every order into their corresponding columns, resulting in a table similar to the one below:<\/p>\n
    OrderID | ProductA | ProductB | ProductC\r\n--------+----------+----------+---------\r\n1       | 5        | 2        | NULL\r\n2       | 3        | NULL     | 1\r\n3       | NULL     | 4        | 3\r\n<\/code><\/pre>\n
    In the examples above, the CASE WHEN statement is used to convert the product names into corresponding columns, and the MAX function is used to aggregate the values of each column. If an order does not have a corresponding product, the column will display a NULL value.<\/p>\n","protected":false},"excerpt":{"rendered":"
    In SQL, you can use the CASE WHEN statement in conjunction with aggregate functions to pivot multiple rows of data into multiple columns. The specific steps are as follows: Organizing by category IF Aggregate functions are applied to the transposed columns for aggregation. Here is an example: consider a table called Orders, which includes columns […]<\/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-17571","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"\nHow to implement converting multiple rows to multiple columns in SQL pivot? - 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-implement-converting-multiple-rows-to-multiple-columns-in-sql-pivot\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to implement converting multiple rows to multiple columns in SQL pivot?\" \/>\n<meta property=\"og:description\" content=\"In SQL, you can use the CASE WHEN statement in conjunction with aggregate functions to pivot multiple rows of data into multiple columns. The specific steps are as follows: Organizing by category IF Aggregate functions are applied to the transposed columns for aggregation. Here is an example: consider a table called Orders, which includes columns […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/how-to-implement-converting-multiple-rows-to-multiple-columns-in-sql-pivot\/\" \/>\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:13:47+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-21T11:13:28+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-implement-converting-multiple-rows-to-multiple-columns-in-sql-pivot\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-implement-converting-multiple-rows-to-multiple-columns-in-sql-pivot\/\"},\"author\":{\"name\":\"Ava Mitchell\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/a3e2658c2cb9fb2be95ae0a8861f4a64\"},\"headline\":\"How to implement converting multiple rows to multiple columns in SQL pivot?\",\"datePublished\":\"2024-03-15T15:13:47+00:00\",\"dateModified\":\"2024-03-21T11:13:28+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-implement-converting-multiple-rows-to-multiple-columns-in-sql-pivot\/\"},\"wordCount\":145,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-implement-converting-multiple-rows-to-multiple-columns-in-sql-pivot\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/how-to-implement-converting-multiple-rows-to-multiple-columns-in-sql-pivot\/\",\"name\":\"How to implement converting multiple rows to multiple columns in SQL pivot? - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-15T15:13:47+00:00\",\"dateModified\":\"2024-03-21T11:13:28+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-implement-converting-multiple-rows-to-multiple-columns-in-sql-pivot\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/how-to-implement-converting-multiple-rows-to-multiple-columns-in-sql-pivot\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-implement-converting-multiple-rows-to-multiple-columns-in-sql-pivot\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to implement converting multiple rows to multiple columns in SQL pivot?\"}]},{\"@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 implement converting multiple rows to multiple columns in SQL pivot? - 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-implement-converting-multiple-rows-to-multiple-columns-in-sql-pivot\/","og_locale":"en_US","og_type":"article","og_title":"How to implement converting multiple rows to multiple columns in SQL pivot?","og_description":"In SQL, you can use the CASE WHEN statement in conjunction with aggregate functions to pivot multiple rows of data into multiple columns. The specific steps are as follows: Organizing by category IF Aggregate functions are applied to the transposed columns for aggregation. Here is an example: consider a table called Orders, which includes columns […]","og_url":"https:\/\/www.silicloud.com\/blog\/how-to-implement-converting-multiple-rows-to-multiple-columns-in-sql-pivot\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-15T15:13:47+00:00","article_modified_time":"2024-03-21T11:13:28+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-implement-converting-multiple-rows-to-multiple-columns-in-sql-pivot\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-implement-converting-multiple-rows-to-multiple-columns-in-sql-pivot\/"},"author":{"name":"Ava Mitchell","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/a3e2658c2cb9fb2be95ae0a8861f4a64"},"headline":"How to implement converting multiple rows to multiple columns in SQL pivot?","datePublished":"2024-03-15T15:13:47+00:00","dateModified":"2024-03-21T11:13:28+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-implement-converting-multiple-rows-to-multiple-columns-in-sql-pivot\/"},"wordCount":145,"commentCount":0,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/how-to-implement-converting-multiple-rows-to-multiple-columns-in-sql-pivot\/","url":"https:\/\/www.silicloud.com\/blog\/how-to-implement-converting-multiple-rows-to-multiple-columns-in-sql-pivot\/","name":"How to implement converting multiple rows to multiple columns in SQL pivot? - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-15T15:13:47+00:00","dateModified":"2024-03-21T11:13:28+00:00","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-implement-converting-multiple-rows-to-multiple-columns-in-sql-pivot\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/how-to-implement-converting-multiple-rows-to-multiple-columns-in-sql-pivot\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/how-to-implement-converting-multiple-rows-to-multiple-columns-in-sql-pivot\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to implement converting multiple rows to multiple columns in SQL pivot?"}]},{"@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\/17571","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=17571"}],"version-history":[{"count":1,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/17571\/revisions"}],"predecessor-version":[{"id":51192,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/17571\/revisions\/51192"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=17571"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=17571"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=17571"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}