{"id":15335,"date":"2024-03-15T10:59:41","date_gmt":"2024-03-15T10:59:41","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/what-are-some-ways-to-pivot-rows-into-columns-in-mysql\/"},"modified":"2025-08-06T17:44:20","modified_gmt":"2025-08-06T17:44:20","slug":"what-are-some-ways-to-pivot-rows-into-columns-in-mysql","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/what-are-some-ways-to-pivot-rows-into-columns-in-mysql\/","title":{"rendered":"What are some ways to pivot rows into columns in MySQL?"},"content":{"rendered":"<p>There are several ways to implement the conversion of rows to columns in MySQL.<\/p>\n<ol>\n<li>With the CASE statement, you can transform row data into column data based on conditions.<\/li>\n<\/ol>\n<pre class=\"post-pre\"><code><span class=\"hljs-keyword\">SELECT<\/span>\r\n    id,\r\n    <span class=\"hljs-built_in\">MAX<\/span>(<span class=\"hljs-keyword\">CASE<\/span> <span class=\"hljs-keyword\">WHEN<\/span> attribute <span class=\"hljs-operator\">=<\/span> <span class=\"hljs-string\">'attribute1'<\/span> <span class=\"hljs-keyword\">THEN<\/span> <span class=\"hljs-keyword\">value<\/span> <span class=\"hljs-keyword\">END<\/span>) <span class=\"hljs-keyword\">AS<\/span> attribute1,\r\n    <span class=\"hljs-built_in\">MAX<\/span>(<span class=\"hljs-keyword\">CASE<\/span> <span class=\"hljs-keyword\">WHEN<\/span> attribute <span class=\"hljs-operator\">=<\/span> <span class=\"hljs-string\">'attribute2'<\/span> <span class=\"hljs-keyword\">THEN<\/span> <span class=\"hljs-keyword\">value<\/span> <span class=\"hljs-keyword\">END<\/span>) <span class=\"hljs-keyword\">AS<\/span> attribute2,\r\n    <span class=\"hljs-built_in\">MAX<\/span>(<span class=\"hljs-keyword\">CASE<\/span> <span class=\"hljs-keyword\">WHEN<\/span> attribute <span class=\"hljs-operator\">=<\/span> <span class=\"hljs-string\">'attribute3'<\/span> <span class=\"hljs-keyword\">THEN<\/span> <span class=\"hljs-keyword\">value<\/span> <span class=\"hljs-keyword\">END<\/span>) <span class=\"hljs-keyword\">AS<\/span> attribute3\r\n<span class=\"hljs-keyword\">FROM<\/span>\r\n    <span class=\"hljs-keyword\">table<\/span>\r\n<span class=\"hljs-keyword\">GROUP<\/span> <span class=\"hljs-keyword\">BY<\/span>\r\n    id;\r\n<\/code><\/pre>\n<ol>\n<li>Pivot function: Although MySQL does not have a built-in pivot function, it is possible to simulate the functionality using subqueries and aggregate functions.<\/li>\n<\/ol>\n<pre class=\"post-pre\"><code><span class=\"hljs-keyword\">SELECT<\/span>\r\n    id,\r\n    <span class=\"hljs-built_in\">MAX<\/span>(<span class=\"hljs-keyword\">CASE<\/span> <span class=\"hljs-keyword\">WHEN<\/span> attribute <span class=\"hljs-operator\">=<\/span> <span class=\"hljs-string\">'attribute1'<\/span> <span class=\"hljs-keyword\">THEN<\/span> <span class=\"hljs-keyword\">value<\/span> <span class=\"hljs-keyword\">END<\/span>) <span class=\"hljs-keyword\">AS<\/span> attribute1,\r\n    <span class=\"hljs-built_in\">MAX<\/span>(<span class=\"hljs-keyword\">CASE<\/span> <span class=\"hljs-keyword\">WHEN<\/span> attribute <span class=\"hljs-operator\">=<\/span> <span class=\"hljs-string\">'attribute2'<\/span> <span class=\"hljs-keyword\">THEN<\/span> <span class=\"hljs-keyword\">value<\/span> <span class=\"hljs-keyword\">END<\/span>) <span class=\"hljs-keyword\">AS<\/span> attribute2,\r\n    <span class=\"hljs-built_in\">MAX<\/span>(<span class=\"hljs-keyword\">CASE<\/span> <span class=\"hljs-keyword\">WHEN<\/span> attribute <span class=\"hljs-operator\">=<\/span> <span class=\"hljs-string\">'attribute3'<\/span> <span class=\"hljs-keyword\">THEN<\/span> <span class=\"hljs-keyword\">value<\/span> <span class=\"hljs-keyword\">END<\/span>) <span class=\"hljs-keyword\">AS<\/span> attribute3\r\n<span class=\"hljs-keyword\">FROM<\/span>\r\n    (<span class=\"hljs-keyword\">SELECT<\/span> id, attribute, <span class=\"hljs-keyword\">value<\/span> <span class=\"hljs-keyword\">FROM<\/span> <span class=\"hljs-keyword\">table<\/span>) <span class=\"hljs-keyword\">AS<\/span> t\r\n<span class=\"hljs-keyword\">GROUP<\/span> <span class=\"hljs-keyword\">BY<\/span>\r\n    id;\r\n<\/code><\/pre>\n<ol>\n<li>Using the GROUP_CONCAT function: It allows you to merge multiple rows of data into one string, which can then be split into multiple columns using a subquery.<\/li>\n<\/ol>\n<pre class=\"post-pre\"><code><span class=\"hljs-keyword\">SELECT<\/span>\r\n    id,\r\n    SUBSTRING_INDEX(GROUP_CONCAT(<span class=\"hljs-keyword\">CASE<\/span> <span class=\"hljs-keyword\">WHEN<\/span> attribute <span class=\"hljs-operator\">=<\/span> <span class=\"hljs-string\">'attribute1'<\/span> <span class=\"hljs-keyword\">THEN<\/span> <span class=\"hljs-keyword\">value<\/span> <span class=\"hljs-keyword\">END<\/span>), <span class=\"hljs-string\">','<\/span>, <span class=\"hljs-number\">1<\/span>) <span class=\"hljs-keyword\">AS<\/span> attribute1,\r\n    SUBSTRING_INDEX(GROUP_CONCAT(<span class=\"hljs-keyword\">CASE<\/span> <span class=\"hljs-keyword\">WHEN<\/span> attribute <span class=\"hljs-operator\">=<\/span> <span class=\"hljs-string\">'attribute2'<\/span> <span class=\"hljs-keyword\">THEN<\/span> <span class=\"hljs-keyword\">value<\/span> <span class=\"hljs-keyword\">END<\/span>), <span class=\"hljs-string\">','<\/span>, <span class=\"hljs-number\">1<\/span>) <span class=\"hljs-keyword\">AS<\/span> attribute2,\r\n    SUBSTRING_INDEX(GROUP_CONCAT(<span class=\"hljs-keyword\">CASE<\/span> <span class=\"hljs-keyword\">WHEN<\/span> attribute <span class=\"hljs-operator\">=<\/span> <span class=\"hljs-string\">'attribute3'<\/span> <span class=\"hljs-keyword\">THEN<\/span> <span class=\"hljs-keyword\">value<\/span> <span class=\"hljs-keyword\">END<\/span>), <span class=\"hljs-string\">','<\/span>, <span class=\"hljs-number\">1<\/span>) <span class=\"hljs-keyword\">AS<\/span> attribute3\r\n<span class=\"hljs-keyword\">FROM<\/span>\r\n    <span class=\"hljs-keyword\">table<\/span>\r\n<span class=\"hljs-keyword\">GROUP<\/span> <span class=\"hljs-keyword\">BY<\/span>\r\n    id;\r\n<\/code><\/pre>\n<p>The above are several common ways to pivot rows to columns in MySQL, and you can choose the most suitable one based on your actual needs.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>There are several ways to implement the conversion of rows to columns in MySQL. With the CASE statement, you can transform row data into column data based on conditions. SELECT id, MAX(CASE WHEN attribute = &#8216;attribute1&#8217; THEN value END) AS attribute1, MAX(CASE WHEN attribute = &#8216;attribute2&#8217; THEN value END) AS attribute2, MAX(CASE WHEN attribute = [&hellip;]<\/p>\n","protected":false},"author":11,"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-15335","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 are some ways to pivot rows into columns in MySQL? - Blog - Silicon Cloud<\/title>\n<meta name=\"description\" content=\"Learn about what are some ways to pivot rows into columns 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-are-some-ways-to-pivot-rows-into-columns-in-mysql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"What are some ways to pivot rows into columns in MySQL?\" \/>\n<meta property=\"og:description\" content=\"Learn about what are some ways to pivot rows into columns in mysql?. Comprehensive guide with examples and best practices.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/what-are-some-ways-to-pivot-rows-into-columns-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-15T10:59:41+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-08-06T17:44:20+00:00\" \/>\n<meta name=\"author\" content=\"Olivia Parker\" \/>\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=\"Olivia Parker\" \/>\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-some-ways-to-pivot-rows-into-columns-in-mysql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/what-are-some-ways-to-pivot-rows-into-columns-in-mysql\/\"},\"author\":{\"name\":\"Olivia Parker\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/3ff7b3da0e45ac5dbbef2502f3cea8d9\"},\"headline\":\"What are some ways to pivot rows into columns in MySQL?\",\"datePublished\":\"2024-03-15T10:59:41+00:00\",\"dateModified\":\"2025-08-06T17:44:20+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/what-are-some-ways-to-pivot-rows-into-columns-in-mysql\/\"},\"wordCount\":117,\"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-are-some-ways-to-pivot-rows-into-columns-in-mysql\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/what-are-some-ways-to-pivot-rows-into-columns-in-mysql\/\",\"name\":\"What are some ways to pivot rows into columns in MySQL? - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-15T10:59:41+00:00\",\"dateModified\":\"2025-08-06T17:44:20+00:00\",\"description\":\"Learn about what are some ways to pivot rows into columns in mysql?. Comprehensive guide with examples and best practices.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/what-are-some-ways-to-pivot-rows-into-columns-in-mysql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/what-are-some-ways-to-pivot-rows-into-columns-in-mysql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/what-are-some-ways-to-pivot-rows-into-columns-in-mysql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"What are some ways to pivot rows into columns 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\/3ff7b3da0e45ac5dbbef2502f3cea8d9\",\"name\":\"Olivia Parker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/56c66f189ba32a6f9eb50f31a38fe774e2a725c213d4070835ccc51b8fbbc54b?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/56c66f189ba32a6f9eb50f31a38fe774e2a725c213d4070835ccc51b8fbbc54b?s=96&d=mm&r=g\",\"caption\":\"Olivia Parker\"},\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/oliviaparker\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"What are some ways to pivot rows into columns in MySQL? - Blog - Silicon Cloud","description":"Learn about what are some ways to pivot rows into columns 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-are-some-ways-to-pivot-rows-into-columns-in-mysql\/","og_locale":"en_US","og_type":"article","og_title":"What are some ways to pivot rows into columns in MySQL?","og_description":"Learn about what are some ways to pivot rows into columns in mysql?. Comprehensive guide with examples and best practices.","og_url":"https:\/\/www.silicloud.com\/blog\/what-are-some-ways-to-pivot-rows-into-columns-in-mysql\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-15T10:59:41+00:00","article_modified_time":"2025-08-06T17:44:20+00:00","author":"Olivia Parker","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"Olivia Parker","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/what-are-some-ways-to-pivot-rows-into-columns-in-mysql\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/what-are-some-ways-to-pivot-rows-into-columns-in-mysql\/"},"author":{"name":"Olivia Parker","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/3ff7b3da0e45ac5dbbef2502f3cea8d9"},"headline":"What are some ways to pivot rows into columns in MySQL?","datePublished":"2024-03-15T10:59:41+00:00","dateModified":"2025-08-06T17:44:20+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/what-are-some-ways-to-pivot-rows-into-columns-in-mysql\/"},"wordCount":117,"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-are-some-ways-to-pivot-rows-into-columns-in-mysql\/","url":"https:\/\/www.silicloud.com\/blog\/what-are-some-ways-to-pivot-rows-into-columns-in-mysql\/","name":"What are some ways to pivot rows into columns in MySQL? - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-15T10:59:41+00:00","dateModified":"2025-08-06T17:44:20+00:00","description":"Learn about what are some ways to pivot rows into columns in mysql?. Comprehensive guide with examples and best practices.","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/what-are-some-ways-to-pivot-rows-into-columns-in-mysql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/what-are-some-ways-to-pivot-rows-into-columns-in-mysql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/what-are-some-ways-to-pivot-rows-into-columns-in-mysql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"What are some ways to pivot rows into columns 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\/3ff7b3da0e45ac5dbbef2502f3cea8d9","name":"Olivia Parker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/56c66f189ba32a6f9eb50f31a38fe774e2a725c213d4070835ccc51b8fbbc54b?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/56c66f189ba32a6f9eb50f31a38fe774e2a725c213d4070835ccc51b8fbbc54b?s=96&d=mm&r=g","caption":"Olivia Parker"},"url":"https:\/\/www.silicloud.com\/blog\/author\/oliviaparker\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/15335","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\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=15335"}],"version-history":[{"count":1,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/15335\/revisions"}],"predecessor-version":[{"id":48800,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/15335\/revisions\/48800"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=15335"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=15335"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=15335"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}