{"id":16327,"date":"2024-03-15T12:44:24","date_gmt":"2024-03-15T12:44:24","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/what-is-the-function-of-the-pivot-function-in-mysql\/"},"modified":"2025-08-07T02:03:40","modified_gmt":"2025-08-07T02:03:40","slug":"what-is-the-function-of-the-pivot-function-in-mysql","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/what-is-the-function-of-the-pivot-function-in-mysql\/","title":{"rendered":"What is the function of the pivot function in MySQL?"},"content":{"rendered":"<p>In MySQL, there is no built-in PIVOT function, but similar functionality can be achieved using CASE statements and aggregate functions.<\/p>\n<p>The main function of the PIVOT function is to transform multiple rows of data into multiple columns identified by a certain column value. It can transpose the data, turning information that was originally in rows into columns.<\/p>\n<p>For example, consider the data in the following table structure:<\/p>\n<pre class=\"post-pre\"><code>Name  | Subject  | Score\r\n------+----------+-------\r\nJohn  | Math     | 90\r\nJohn  | English  | 80\r\nJohn  | Science  | 85\r\nAmy   | Math     | 95\r\nAmy   | English  | 75\r\nAmy   | Science  | 90\r\n<\/code><\/pre>\n<p>To transpose the above data by subject, you can use the PIVOT function. The transformed result is as follows:<\/p>\n<pre class=\"post-pre\"><code>Name  | Math | English | Science\r\n------+------+------+--------\r\nJohn  | 90   | 80      | 85\r\nAmy   | 95   | 75      | 90\r\n<\/code><\/pre>\n<p>This way, each student&#8217;s grades are displayed as a row of data, with each subject&#8217;s grades listed in separate columns.<\/p>\n<p>Although MySQL does not have a built-in PIVOT function, you can manually achieve transposition using the CASE statement and aggregate functions. You can refer to the following example code for the specific implementation.<\/p>\n<pre class=\"post-pre\"><code><span class=\"hljs-keyword\">SELECT<\/span> Name,\r\n       <span class=\"hljs-built_in\">MAX<\/span>(<span class=\"hljs-keyword\">CASE<\/span> <span class=\"hljs-keyword\">WHEN<\/span> Subject <span class=\"hljs-operator\">=<\/span> <span class=\"hljs-string\">'Math'<\/span> <span class=\"hljs-keyword\">THEN<\/span> Score <span class=\"hljs-keyword\">END<\/span>) <span class=\"hljs-keyword\">AS<\/span> Math,\r\n       <span class=\"hljs-built_in\">MAX<\/span>(<span class=\"hljs-keyword\">CASE<\/span> <span class=\"hljs-keyword\">WHEN<\/span> Subject <span class=\"hljs-operator\">=<\/span> <span class=\"hljs-string\">'English'<\/span> <span class=\"hljs-keyword\">THEN<\/span> Score <span class=\"hljs-keyword\">END<\/span>) <span class=\"hljs-keyword\">AS<\/span> English,\r\n       <span class=\"hljs-built_in\">MAX<\/span>(<span class=\"hljs-keyword\">CASE<\/span> <span class=\"hljs-keyword\">WHEN<\/span> Subject <span class=\"hljs-operator\">=<\/span> <span class=\"hljs-string\">'Science'<\/span> <span class=\"hljs-keyword\">THEN<\/span> Score <span class=\"hljs-keyword\">END<\/span>) <span class=\"hljs-keyword\">AS<\/span> Science\r\n<span class=\"hljs-keyword\">FROM<\/span> TableName\r\n<span class=\"hljs-keyword\">GROUP<\/span> <span class=\"hljs-keyword\">BY<\/span> Name;\r\n<\/code><\/pre>\n<p>In the code above, a CASE statement is used to place grades of different subjects into corresponding columns, and the MAX function is used to retrieve the highest grade for each subject. Finally, the data is transposed by using the GROUP BY statement to group by name.<\/p>\n<p>It should be noted that the implementation of the PIVOT function may vary depending on the database, and the code above is just one common implementation method.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In MySQL, there is no built-in PIVOT function, but similar functionality can be achieved using CASE statements and aggregate functions. The main function of the PIVOT function is to transform multiple rows of data into multiple columns identified by a certain column value. It can transpose the data, turning information that was originally in rows [&hellip;]<\/p>\n","protected":false},"author":13,"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-16327","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 function of the pivot function in MySQL? - Blog - Silicon Cloud<\/title>\n<meta name=\"description\" content=\"Learn about what is the function 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-function-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 function of the pivot function in MySQL?\" \/>\n<meta property=\"og:description\" content=\"Learn about what is the function 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-function-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:44:24+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-08-07T02:03:40+00:00\" \/>\n<meta name=\"author\" content=\"Isabella Edwards\" \/>\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=\"Isabella Edwards\" \/>\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-function-of-the-pivot-function-in-mysql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/what-is-the-function-of-the-pivot-function-in-mysql\/\"},\"author\":{\"name\":\"Isabella Edwards\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/5579144e23c225c8188167f3e3f888dd\"},\"headline\":\"What is the function of the pivot function in MySQL?\",\"datePublished\":\"2024-03-15T12:44:24+00:00\",\"dateModified\":\"2025-08-07T02:03:40+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/what-is-the-function-of-the-pivot-function-in-mysql\/\"},\"wordCount\":225,\"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-function-of-the-pivot-function-in-mysql\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/what-is-the-function-of-the-pivot-function-in-mysql\/\",\"name\":\"What is the function of the pivot function in MySQL? - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-15T12:44:24+00:00\",\"dateModified\":\"2025-08-07T02:03:40+00:00\",\"description\":\"Learn about what is the function of the pivot function in mysql?. Comprehensive guide with examples and best practices.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/what-is-the-function-of-the-pivot-function-in-mysql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/what-is-the-function-of-the-pivot-function-in-mysql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/what-is-the-function-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 function 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\/5579144e23c225c8188167f3e3f888dd\",\"name\":\"Isabella Edwards\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d4d4dec47f553ac7961d9fa4cc9bdcdcf5b7ce5106594330b6d25c5694fdbaec?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d4d4dec47f553ac7961d9fa4cc9bdcdcf5b7ce5106594330b6d25c5694fdbaec?s=96&d=mm&r=g\",\"caption\":\"Isabella Edwards\"},\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/isabellaedwards\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"What is the function of the pivot function in MySQL? - Blog - Silicon Cloud","description":"Learn about what is the function 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-function-of-the-pivot-function-in-mysql\/","og_locale":"en_US","og_type":"article","og_title":"What is the function of the pivot function in MySQL?","og_description":"Learn about what is the function of the pivot function in mysql?. Comprehensive guide with examples and best practices.","og_url":"https:\/\/www.silicloud.com\/blog\/what-is-the-function-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:44:24+00:00","article_modified_time":"2025-08-07T02:03:40+00:00","author":"Isabella Edwards","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"Isabella Edwards","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/what-is-the-function-of-the-pivot-function-in-mysql\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/what-is-the-function-of-the-pivot-function-in-mysql\/"},"author":{"name":"Isabella Edwards","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/5579144e23c225c8188167f3e3f888dd"},"headline":"What is the function of the pivot function in MySQL?","datePublished":"2024-03-15T12:44:24+00:00","dateModified":"2025-08-07T02:03:40+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/what-is-the-function-of-the-pivot-function-in-mysql\/"},"wordCount":225,"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-function-of-the-pivot-function-in-mysql\/","url":"https:\/\/www.silicloud.com\/blog\/what-is-the-function-of-the-pivot-function-in-mysql\/","name":"What is the function of the pivot function in MySQL? - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-15T12:44:24+00:00","dateModified":"2025-08-07T02:03:40+00:00","description":"Learn about what is the function of the pivot function in mysql?. Comprehensive guide with examples and best practices.","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/what-is-the-function-of-the-pivot-function-in-mysql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/what-is-the-function-of-the-pivot-function-in-mysql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/what-is-the-function-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 function 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\/5579144e23c225c8188167f3e3f888dd","name":"Isabella Edwards","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/d4d4dec47f553ac7961d9fa4cc9bdcdcf5b7ce5106594330b6d25c5694fdbaec?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d4d4dec47f553ac7961d9fa4cc9bdcdcf5b7ce5106594330b6d25c5694fdbaec?s=96&d=mm&r=g","caption":"Isabella Edwards"},"url":"https:\/\/www.silicloud.com\/blog\/author\/isabellaedwards\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/16327","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\/13"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=16327"}],"version-history":[{"count":1,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/16327\/revisions"}],"predecessor-version":[{"id":49878,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/16327\/revisions\/49878"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=16327"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=16327"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=16327"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}