{"id":16335,"date":"2024-03-15T12:45:20","date_gmt":"2024-03-15T12:45:20","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-pivot-function-in-mysql\/"},"modified":"2025-08-07T02:07:41","modified_gmt":"2025-08-07T02:07:41","slug":"what-is-the-usage-of-the-pivot-function-in-mysql","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-pivot-function-in-mysql\/","title":{"rendered":"What is the usage of the pivot function in MySQL?"},"content":{"rendered":"<p>Although there is no built-in PIVOT function in MySQL, you can achieve similar functionality using conditional aggregation and the CASE statement.<\/p>\n<p>In MySQL, you can use conditional aggregation and the CASE statement to pivot rows into columns. Here is an example illustrating how to achieve the PIVOT functionality using conditional aggregation and the CASE statement.<\/p>\n<p>Assume we have a table named sales, which includes the following data:<\/p>\n<pre class=\"post-pre\"><code>+-------+-------+-------+-------+\r\n| Month | Sales | Region| Product|\r\n+-------+-------+-------+-------+\r\n|   1   |  100  |   A   |   X   |\r\n|   1   |  200  |   B   |   X   |\r\n|   2   |  150  |   A   |   Y   |\r\n|   2   |  250  |   B   |   Y   |\r\n+-------+-------+-------+-------+\r\n<\/code><\/pre>\n<p>We want to pivot the values in the Region column as columns and the values in the Sales column as the corresponding values, resulting in the following outcome:<\/p>\n<pre class=\"post-pre\"><code>+-------+-------+-------+\r\n| Month |   A   |   B   |\r\n+-------+-------+-------+\r\n|   1   |  100  |  200  |\r\n|   2   |  150  |  250  |\r\n+-------+-------+-------+\r\n<\/code><\/pre>\n<p>The following query can achieve this purpose:<\/p>\n<pre class=\"post-pre\"><code><span class=\"hljs-keyword\">SELECT<\/span> \r\n  <span class=\"hljs-keyword\">Month<\/span>,\r\n  <span class=\"hljs-built_in\">SUM<\/span>(<span class=\"hljs-keyword\">CASE<\/span> <span class=\"hljs-keyword\">WHEN<\/span> Region <span class=\"hljs-operator\">=<\/span> <span class=\"hljs-string\">'A'<\/span> <span class=\"hljs-keyword\">THEN<\/span> Sales <span class=\"hljs-keyword\">ELSE<\/span> <span class=\"hljs-number\">0<\/span> <span class=\"hljs-keyword\">END<\/span>) <span class=\"hljs-keyword\">AS<\/span> A,\r\n  <span class=\"hljs-built_in\">SUM<\/span>(<span class=\"hljs-keyword\">CASE<\/span> <span class=\"hljs-keyword\">WHEN<\/span> Region <span class=\"hljs-operator\">=<\/span> <span class=\"hljs-string\">'B'<\/span> <span class=\"hljs-keyword\">THEN<\/span> Sales <span class=\"hljs-keyword\">ELSE<\/span> <span class=\"hljs-number\">0<\/span> <span class=\"hljs-keyword\">END<\/span>) <span class=\"hljs-keyword\">AS<\/span> B\r\n<span class=\"hljs-keyword\">FROM<\/span> sales\r\n<span class=\"hljs-keyword\">GROUP<\/span> <span class=\"hljs-keyword\">BY<\/span> <span class=\"hljs-keyword\">Month<\/span>;\r\n<\/code><\/pre>\n<p>This query utilizes conditional aggregation and CASE statements.<\/p>\n<ol>\n<li>For each unique Month value, add up the Sales values that meet the criteria using the SUM function, creating new columns A and B.<\/li>\n<li>The CASE statement is used to return different results (Sales value or 0) based on conditions (Region value).<\/li>\n<\/ol>\n<p>The outcome will be grouped based on the Month and new columns A and B will be created. Column A will contain Sales values for Region &#8216;A&#8217;, while column B will contain Sales values for Region &#8216;B&#8217;.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Although there is no built-in PIVOT function in MySQL, you can achieve similar functionality using conditional aggregation and the CASE statement. In MySQL, you can use conditional aggregation and the CASE statement to pivot rows into columns. Here is an example illustrating how to achieve the PIVOT functionality using conditional aggregation and the CASE statement. [&hellip;]<\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","footnotes":""},"categories":[1],"tags":[453,1402,299,1404,1403],"class_list":["post-16335","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-development","tag-guide","tag-programming","tag-technology","tag-tutorial"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.5 (Yoast SEO v21.5) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>What is the usage of the pivot function in MySQL? - Blog - Silicon Cloud<\/title>\n<meta name=\"description\" content=\"Learn about what is the usage of the pivot function in mysql?. Comprehensive guide with examples and best practices.\" \/>\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-is-the-usage-of-the-pivot-function-in-mysql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"What is the usage of the pivot function in MySQL?\" \/>\n<meta property=\"og:description\" content=\"Learn about what is the usage of the pivot function in mysql?. Comprehensive guide with examples and best practices.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-pivot-function-in-mysql\/\" \/>\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-15T12:45:20+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-08-07T02:07:41+00:00\" \/>\n<meta name=\"author\" content=\"Benjamin Taylor\" \/>\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=\"Benjamin Taylor\" \/>\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-is-the-usage-of-the-pivot-function-in-mysql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-pivot-function-in-mysql\/\"},\"author\":{\"name\":\"Benjamin Taylor\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/ac801fe9549a25960ce48aa2e0a691c9\"},\"headline\":\"What is the usage of the pivot function in MySQL?\",\"datePublished\":\"2024-03-15T12:45:20+00:00\",\"dateModified\":\"2025-08-07T02:07:41+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-pivot-function-in-mysql\/\"},\"wordCount\":198,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"keywords\":[\"Development\",\"guide\",\"programming\",\"technology\",\"tutorial\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-pivot-function-in-mysql\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-pivot-function-in-mysql\/\",\"name\":\"What is the usage of the pivot function in MySQL? - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-15T12:45:20+00:00\",\"dateModified\":\"2025-08-07T02:07:41+00:00\",\"description\":\"Learn about what is the usage of the pivot function in mysql?. Comprehensive guide with examples and best practices.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-pivot-function-in-mysql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-pivot-function-in-mysql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-pivot-function-in-mysql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"What is the usage of the pivot function in MySQL?\"}]},{\"@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\/ac801fe9549a25960ce48aa2e0a691c9\",\"name\":\"Benjamin Taylor\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/ec2e3d3e2d525fd148047c4520ae7c1cdccd1f4b48a1a488422b31f04f345c14?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/ec2e3d3e2d525fd148047c4520ae7c1cdccd1f4b48a1a488422b31f04f345c14?s=96&d=mm&r=g\",\"caption\":\"Benjamin Taylor\"},\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/benjamintaylor\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"What is the usage of the pivot function in MySQL? - Blog - Silicon Cloud","description":"Learn about what is the usage of the pivot function in mysql?. Comprehensive guide with examples and best practices.","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-is-the-usage-of-the-pivot-function-in-mysql\/","og_locale":"en_US","og_type":"article","og_title":"What is the usage of the pivot function in MySQL?","og_description":"Learn about what is the usage of the pivot function in mysql?. Comprehensive guide with examples and best practices.","og_url":"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-pivot-function-in-mysql\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-15T12:45:20+00:00","article_modified_time":"2025-08-07T02:07:41+00:00","author":"Benjamin Taylor","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"Benjamin Taylor","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-pivot-function-in-mysql\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-pivot-function-in-mysql\/"},"author":{"name":"Benjamin Taylor","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/ac801fe9549a25960ce48aa2e0a691c9"},"headline":"What is the usage of the pivot function in MySQL?","datePublished":"2024-03-15T12:45:20+00:00","dateModified":"2025-08-07T02:07:41+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-pivot-function-in-mysql\/"},"wordCount":198,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"keywords":["Development","guide","programming","technology","tutorial"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-pivot-function-in-mysql\/","url":"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-pivot-function-in-mysql\/","name":"What is the usage of the pivot function in MySQL? - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-15T12:45:20+00:00","dateModified":"2025-08-07T02:07:41+00:00","description":"Learn about what is the usage of the pivot function in mysql?. Comprehensive guide with examples and best practices.","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-pivot-function-in-mysql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-pivot-function-in-mysql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-pivot-function-in-mysql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"What is the usage of the pivot function in MySQL?"}]},{"@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\/ac801fe9549a25960ce48aa2e0a691c9","name":"Benjamin Taylor","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/ec2e3d3e2d525fd148047c4520ae7c1cdccd1f4b48a1a488422b31f04f345c14?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ec2e3d3e2d525fd148047c4520ae7c1cdccd1f4b48a1a488422b31f04f345c14?s=96&d=mm&r=g","caption":"Benjamin Taylor"},"url":"https:\/\/www.silicloud.com\/blog\/author\/benjamintaylor\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/16335","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\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=16335"}],"version-history":[{"count":1,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/16335\/revisions"}],"predecessor-version":[{"id":49887,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/16335\/revisions\/49887"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=16335"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=16335"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=16335"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}