{"id":3006,"date":"2024-06-05T07:39:57","date_gmt":"2024-06-04T23:39:57","guid":{"rendered":"https:\/\/five.co\/?p=3006"},"modified":"2025-04-15T16:27:46","modified_gmt":"2025-04-15T08:27:46","slug":"mysql-transpose-rows-to-columns","status":"publish","type":"post","link":"https:\/\/preview-php85.dzine.org\/five\/blog\/mysql-transpose-rows-to-columns\/","title":{"rendered":"MySQL: Transpose Rows to Columns (How to Guide)"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">There is always situations where information is spread across multiple rows, making analysis and reporting a challenge. This is where the concept of data transposition comes in. Transposing rows to columns essentially flips the table, organizing your data in a more column-oriented way.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For example, if you have sales figures for different product categories across various months. Transposing rows to columns can bring all those monthly sales figures together under each product category, creating a cleaner and more concise view for reports or further analysis. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This article will specifically guide you through various methods for achieving this data transformation in MySQL, focusing on the popular <code>sql transpose rows to columns<\/code> techniques.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<div style=\"background-color: #001524;\"><hr style=\"height: 5px;\" \/>\n<pre style=\"text-align: center; overflow: hidden; white-space: pre-line;\"><span style=\"color: #f1ebda; background-color: #4588d8; font-size: calc(25px + 0.390625vw);\"><strong>SQL Made Simple - Try Five<\/strong><br \/><span style=\"font-size: 14pt;\">Five is the Easiest Way to Create, Model and Query a SQL Database<\/span><\/span><\/pre>\n<p style=\"text-align: center;\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\" target=\"_blank\" rel=\"noopener\"><button style=\"background-color: #f8b92b; border: none; color: black; padding: 20px; text-align: center; text-decoration: none; display: inline-block; font-size: 18px; cursor: pointer; margin: 4px 2px; border-radius: 5px;\"><strong>Get Started<\/strong><\/button><\/a><br \/><span style=\"color: #ffffff; font-size: revert;\">___<\/span><\/p>\n<p style=\"text-align: center;\"><span style=\"text-decoration: underline;\"><strong><span style=\"color: #ffffff; text-decoration: underline;\">Here&#8217;s What You Can Do With Five<\/span><\/strong><\/span><\/p>\n<p style=\"text-align: center;\"><span style=\"color: #ffffff;\">Create and Model a SQL Database \u2705<br \/>Write or Build SQL Queries \u2705<br \/>Visualize Queries as Charts or in Dashboards \u2705<br \/>Add CRUD Permissions to Control Data Access \u2705<br \/>Host Your Database Online \u2705\u00a0<\/span><\/p>\n<p style=\"text-align: center;\"><span style=\"color: #f1ebda; background-color: #4588d8;\"><em><strong>&#8220;Five bridges the gap between SQL and the web, <br \/>allowing me to create full-stack applications almost entirely in SQL&#8221;<\/strong><\/em><\/span><br \/><span style=\"color: #ffffff;\">&#8211; Crag Jones, Database Administrator (DBA)<\/span><\/p>\n<hr style=\"height: 5px;\" \/><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n<div class=\"root-eb-toc-wd2m9 wp-block-essential-blocks-table-of-contents\"><div class=\"eb-parent-wrapper eb-parent-eb-toc-wd2m9 \"><div class=\"eb-toc-container eb-toc-wd2m9  eb-toc-is-not-sticky eb-toc-not-collapsible eb-toc-initially-not-collapsed eb-toc-scrollToTop style-1 list-style-none\" data-scroll-top=\"false\" data-scroll-top-icon=\"fas fa-angle-up\" data-collapsible=\"false\" data-sticky-hide-mobile=\"false\" data-sticky=\"false\" data-scroll-target=\"scroll_to_toc\" data-copy-link=\"false\" data-editor-type=\"\" data-hide-desktop=\"false\" data-hide-tab=\"false\" data-hide-mobile=\"false\" data-itemcollapsed=\"false\" data-highlight-scroll=\"false\"><div class=\"eb-toc-header\"><h2 class=\"eb-toc-title\">Table of Contents<\/h2><\/div><div class=\"eb-toc-wrapper \" data-headers=\"[{&quot;level&quot;:2,&quot;content&quot;:&quot;Methods for Transposing Rows to Columns&quot;,&quot;text&quot;:&quot;Methods for Transposing Rows to Columns&quot;,&quot;link&quot;:&quot;methods-for-transposing-rows-to-columns&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;PIVOT Function:&quot;,&quot;text&quot;:&quot;PIVOT Function:&quot;,&quot;link&quot;:&quot;pivot-function&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;CASE WHEN with GROUP BY:&quot;,&quot;text&quot;:&quot;CASE WHEN with GROUP BY:&quot;,&quot;link&quot;:&quot;case-when-with-group-by&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;XML Method:&quot;,&quot;text&quot;:&quot;XML Method:&quot;,&quot;link&quot;:&quot;xml-method&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Conclusion - MySQL: Transpose Rows to Columns&quot;,&quot;text&quot;:&quot;Conclusion - MySQL: Transpose Rows to Columns&quot;,&quot;link&quot;:&quot;conclusion-mysql-transpose-rows-to-columns&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Bonus: Transposing Columns to Rows with UNPIVOT&quot;,&quot;text&quot;:&quot;Bonus: Transposing Columns to Rows with UNPIVOT&quot;,&quot;link&quot;:&quot;bonus-transposing-columns-to-rows-with-unpivot&quot;}]\" data-visible=\"[true,true,true,true,true,true]\" data-delete-headers=\"[{&quot;label&quot;:&quot;Methods for Transposing Rows to Columns&quot;,&quot;value&quot;:&quot;methods-for-transposing-rows-to-columns&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;PIVOT Function:&quot;,&quot;value&quot;:&quot;pivot-function&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;CASE WHEN with GROUP BY:&quot;,&quot;value&quot;:&quot;case-when-with-group-by&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;XML Method:&quot;,&quot;value&quot;:&quot;xml-method&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Conclusion - MySQL: Transpose Rows to Columns&quot;,&quot;value&quot;:&quot;conclusion-mysql-transpose-rows-to-columns&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Bonus: Transposing Columns to Rows with UNPIVOT&quot;,&quot;value&quot;:&quot;bonus-transposing-columns-to-rows-with-unpivot&quot;,&quot;isDelete&quot;:false}]\" data-smooth=\"true\" data-top-offset=\"\"><div class=\"eb-toc__list-wrap\"><ul class=\"eb-toc__list\"><li><a href=\"#methods-for-transposing-rows-to-columns\">Methods for Transposing Rows to Columns<\/a><ul class=\"eb-toc__list\"><li><a href=\"#pivot-function\">PIVOT Function:<\/a><li><a href=\"#case-when-with-group-by\">CASE WHEN with GROUP BY:<\/a><li><a href=\"#xml-method\">XML Method:<\/a><\/li><\/ul><li><a href=\"#conclusion-mysql-transpose-rows-to-columns\">Conclusion &#8211; MySQL: Transpose Rows to Columns<\/a><li><a href=\"#bonus-transposing-columns-to-rows-with-unpivot\">Bonus: Transposing Columns to Rows with UNPIVOT<\/a><\/ul><\/div><\/div><\/div><\/div><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Methods for Transposing Rows to Columns<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">PIVOT Function:<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The PIVOT function is a powerful tool in MySQL for transposing rows to columns. It allows you to reorganize data based on a specific column value, bringing together related information into a single row. Here&#8217;s a breakdown of the PIVOT function and its usage:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Syntax:<\/strong><\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT\n  [original_column1],\n  [original_column2],\n  ...\n  PIVOT (\n    aggregate_function(target_value)\n    FOR pivot_column IN (value1, value2, ...)\n  ) AS pivoted_data\nFROM source_table;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #E06C75\">[original_column1]<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #E06C75\">[original_column2]<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  ...<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  PIVOT (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    aggregate_function(target_value)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FOR<\/span><span style=\"color: #ABB2BF\"> pivot_column <\/span><span style=\"color: #C678DD\">IN<\/span><span style=\"color: #ABB2BF\"> (value1, value2, ...)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  ) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> pivoted_data<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> source_table;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:10px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Explanation:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code class=\"\">[original_column1]<\/code>, <code class=\"\">[original_column2]<\/code>: These represent columns you want to retain from the original table.<\/li>\n\n\n\n<li><code class=\"\">aggregate_function<\/code>: This specifies the function to use for aggregating the target values (e.g., SUM, AVG, COUNT).<\/li>\n\n\n\n<li><code class=\"\">target_value<\/code>: This refers to the column containing the data points you want to bring into new columns.<\/li>\n\n\n\n<li><code class=\"\">pivot_column<\/code>: This is the column that will define the new column names based on its distinct values (e.g., month, product category).<\/li>\n\n\n\n<li><code class=\"\">(value1, value2, ...)<\/code>: This list explicitly defines the values from the <code class=\"\">pivot_column<\/code> that will be used as new column names.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"has-text-align-center wp-block-paragraph\"><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-cyan-blue-color\"><strong style=\"background-color: rgb(69, 136, 216); color: rgb(241, 235, 218); font-size: calc(12px + 0.390625vw); font-weight: bold;\"><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-cyan-blue-color\"><strong style=\"background-color: rgb(69, 136, 216); color: rgb(241, 235, 218); font-size: calc(12px + 0.390625vw); font-weight: bold;\">Test<\/strong><\/mark> Your Data Transposition Skills With Five<\/strong> <\/mark><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you are learning about transposing rows to columns, Five is the perfect platform to practice your SQL skills. Five is a development environment pre-packaged with a ready-to-use, customizable MySQL. Five also lets you write queries and build a responsive admin panel on top of your data.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img width=\"1024\" height=\"576\" src=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/06\/Five.Co-SQL-Dashboard-1-1024x576-1.png\" alt=\"\" class=\"wp-image-3008\" srcset=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/06\/Five.Co-SQL-Dashboard-1-1024x576-1.png 1024w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/06\/Five.Co-SQL-Dashboard-1-1024x576-1-300x169.png 300w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/06\/Five.Co-SQL-Dashboard-1-1024x576-1-768x432.png 768w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"wp-block-paragraph\">Often the best way to learn is to build a web application for yourself. Five makes this process a lot easier and doesn&#8217;t completely throw you into the deep end. Plus there are plenty of guides available to help you get started including this one: <a href=\"https:\/\/preview-php85.dzine.org\/five\/code-along\/code-along-full-stack-web-app\/\">Build &amp; Deploy a Full-Stack Web App<\/a><\/p>\n\n\n\n<div style=\"background-color: #001524;\"><hr style=\"height: 5px;\">\n<pre style=\"text-align: center; overflow: hidden; white-space: pre-line;\"><span style=\"color: #f1ebda; background-color: #4588d8; font-size: calc(18px + 0.390625vw);\"><strong>Build Your Web App In 4 Steps<\/strong><br><span style=\"font-size: 14pt;\">Start Developing and Learning For Free<\/span><\/span><\/pre>\n<p style=\"text-align: center;\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\" target=\"_blank\" rel=\"noopener\"><button style=\"background-color: #f8b92b; border: none; color: black; padding: 20px; text-align: center; text-decoration: none; display: inline-block; font-size: 18px; cursor: pointer; margin: 4px 2px; border-radius: 5px;\"><strong>Get Instant Access<\/strong><\/button><br><\/a><\/p>\n<hr style=\"height: 5px;\"><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Example:<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s consider a table named <code class=\"\">sales<\/code> with columns <code class=\"\">product<\/code>, <code class=\"\">month<\/code>, and <code class=\"\">sales_amount<\/code>. We want to transpose the data to show monthly sales figures for each product.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT product,\n  PIVOT (\n    SUM(sales_amount)\n    FOR month IN ('Jan', 'Feb', 'Mar')\n  ) AS sales_by_month\nFROM sales;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> product,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  PIVOT (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(sales_amount)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FOR<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">month<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">IN<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #98C379\">&#39;Jan&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;Feb&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;Mar&#39;<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  ) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> sales_by_month<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:10px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query will create a new table structure with the product names in the first column. It will then add three new columns named &#8216;Jan&#8217;, &#8216;Feb&#8217;, and &#8216;Mar&#8217; containing the total sales amount for each product in those respective months.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Limitations:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The PIVOT function requires you to pre-define the list of new column names based on the distinct values in the <code>pivot_column<\/code>. This can be limiting if you have a large or dynamic number of columns.<\/li>\n\n\n\n<li>PIVOT might become complex for intricate data manipulation scenarios.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Despite these limitations, the PIVOT function remains a valuable method for transposing rows to columns, especially when you have a well-defined set of new column names.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">CASE WHEN with GROUP BY:<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The <code class=\"\">CASE WHEN<\/code> expressions combined with <code class=\"\">GROUP BY<\/code> offer another effective way to transpose rows to columns in MySQL. This approach leverages conditional logic to dynamically create new columns based on your data. Here&#8217;s how it works:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Explanation:<\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong><code>CASE WHEN<\/code> Expressions:<\/strong> We use multiple <code class=\"\">CASE WHEN<\/code> statements to check the value of a specific column (often the one defining the &#8220;pivot&#8221;).<\/li>\n\n\n\n<li><strong>Conditional Assignment:<\/strong> Within each <code class=\"\">CASE WHEN<\/code> statement, we assign a value to a new column name that reflects the condition being checked.<\/li>\n\n\n\n<li><strong><code>GROUP BY<\/code> Clause:<\/strong> The <code class=\"\">GROUP BY<\/code> clause groups the data based on the original columns you want to retain.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Advantages:<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This method shines when you have a <strong>dynamic number of columns<\/strong> to create. Unlike the PIVOT function, you don&#8217;t need to pre-define the list of new column names. The <code class=\"\">CASE WHEN<\/code> expressions handle that dynamically based on the data itself.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Example:<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Imagine a table named <code class=\"\">customers<\/code> with columns <code class=\"\">customer_id<\/code>, <code class=\"\">city<\/code>, and <code>country<\/code>. We want to transpose the data to show the number of customers from each city within each country.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT customer_id, country,\n  SUM(CASE WHEN city = 'New York' THEN 1 ELSE 0 END) AS num_customers_NY,\n  SUM(CASE WHEN city = 'Los Angeles' THEN 1 ELSE 0 END) AS num_customers_LA,\n  ... (Add more CASE WHEN statements for other cities)\nFROM customers\nGROUP BY customer_id, country;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> customer_id, country,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> city <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;New York&#39;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">ELSE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">0<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> num_customers_NY,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> city <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;Los Angeles&#39;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">ELSE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">0<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> num_customers_LA,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  ... (<\/span><span style=\"color: #C678DD\">Add<\/span><span style=\"color: #ABB2BF\"> more <\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> statements <\/span><span style=\"color: #C678DD\">for<\/span><span style=\"color: #ABB2BF\"> other cities)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> customers<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> customer_id, country;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:10px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query will create a result set with the customer ID and country in the first two columns. It will then dynamically generate new columns named <code>num_customers_NY<\/code>, <code>num_customers_LA<\/code>, and so on, based on the distinct city values present. Each new column reflects the count of customers from that specific city within each country.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">XML Method:<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">For experienced users seeking an alternative approach to transposing rows to columns with even greater flexibility, the XML method exists. This technique leverages MySQL&#8217;s XML capabilities to manipulate data and achieve the desired transformation.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Why Consider XML?<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Similar to the <code class=\"\">CASE WHEN<\/code> approach, the XML method excels when dealing with <strong>dynamic columns<\/strong>. It doesn&#8217;t require pre-defined column names, dynamically generating them based on your data.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Targeted for Advanced Users:<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">While powerful, the XML method involves more intricate steps compared to the previously discussed methods. It&#8217;s recommended for users comfortable with XML functions in MySQL.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Further Exploration:<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you&#8217;re interested in delving deeper into the XML method for transposing data, you can find a comprehensive explanation <a href=\"https:\/\/www.sqlshack.com\/multiple-options-to-transposing-rows-into-columns\/#:~:text=The%20XML%20option%20to%20transposing,such%20as%20STUFF%20and%20QUOTENAME.\" rel=\"nofollow noopener\" target=\"_blank\">here<\/a>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion &#8211; MySQL: Transpose Rows to Columns<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">We explored three primary methods:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>PIVOT Function:<\/strong> Ideal for scenarios with a well-defined set of new columns based on distinct values.<\/li>\n\n\n\n<li><strong>CASE WHEN with GROUP BY:<\/strong> A flexible approach that thrives when dealing with dynamic columns.<\/li>\n\n\n\n<li><strong>XML Method (Optional):<\/strong> An advanced technique offering even greater control for experienced users.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Remember, the best method depends on your specific data structure and the number of columns you need to create. Consider the factors mentioned throughout this guide to make the best choice.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Bonus: Transposing Columns to Rows with UNPIVOT<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">While this article focused on transposing rows to columns, MySQL offers another handy function for those who might have encountered the opposite situation: data spread across multiple columns that you want to consolidate into rows. Introducing the <code>UNPIVOT<\/code> function.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>UNPIVOT in Action:<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The <code>UNPIVOT<\/code> function essentially reverses the logic of <code>PIVOT<\/code>, taking data from multiple columns and transforming it into rows. This can be beneficial for tasks like:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Data normalization:<\/strong> Simplifying complex table structures with many columns.<\/li>\n\n\n\n<li><strong>Reporting:<\/strong> Presenting information from various columns in a single row for easier analysis.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Example:<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Imagine a table named <code class=\"\">sales_by_month<\/code> with columns <code class=\"\">product<\/code>, <code class=\"\">Jan<\/code>, <code class=\"\">Feb<\/code>, <code class=\"\">Mar<\/code>. This table stores monthly sales figures for each product. We can use UNPIVOT to transform this data into a structure with separate rows for each month, showing the product and its corresponding sales amount for that month.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s the UNPIVOT query:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT product,\n  month,\n  sales_amount\nFROM sales_by_month\nUNPIVOT (\n  sales_amount FOR month IN (Jan, Feb, Mar)\n) AS unpivoted_data;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> product,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #C678DD\">month<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  sales_amount<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales_by_month<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">UNPIVOT (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  sales_amount <\/span><span style=\"color: #C678DD\">FOR<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">month<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">IN<\/span><span style=\"color: #ABB2BF\"> (Jan, Feb, Mar)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> unpivoted_data;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:9px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Explanation:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>This query selects the <code>product<\/code> column and creates new columns named <code>month<\/code> and <code>sales_amount<\/code>.<\/li>\n\n\n\n<li>The <code>UNPIVOT<\/code> clause uses the <code>Jan<\/code>, <code>Feb<\/code>, and <code>Mar<\/code> values as the source for the new <code>month<\/code> column.<\/li>\n\n\n\n<li>The <code>sales_amount<\/code> column is populated with the corresponding sales figures based on the chosen month.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">The result will be a table with one row for each product in the original table, and three additional columns: <code>product<\/code>, <code>month<\/code> (containing &#8220;Jan&#8221;, &#8220;Feb&#8221;, or &#8220;Mar&#8221;), and <code>sales_amount<\/code> showing the sales figure for that specific product in that month.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>There is always situations where information is spread across multiple rows, making analysis and reporting a challenge. This is where the concept of data transposition comes in. Transposing rows to columns essentially flips the table, organizing your data in a more column-oriented way. For example, if you have sales figures for different product categories across [&hellip;]<\/p>\n","protected":false},"author":11,"featured_media":3007,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_eb_attr":"","_uag_custom_page_level_css":"","footnotes":""},"categories":[21],"tags":[],"class_list":["post-3006","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog"],"acf":[],"uagb_featured_image_src":{"full":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/06\/Five.Co-MySQL-Transpose-Rows-to-Columns.png",1280,720,false],"thumbnail":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/06\/Five.Co-MySQL-Transpose-Rows-to-Columns-150x150.png",150,150,true],"medium":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/06\/Five.Co-MySQL-Transpose-Rows-to-Columns-300x169.png",300,169,true],"medium_large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/06\/Five.Co-MySQL-Transpose-Rows-to-Columns-768x432.png",768,432,true],"large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/06\/Five.Co-MySQL-Transpose-Rows-to-Columns-1024x576.png",1024,576,true],"1536x1536":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/06\/Five.Co-MySQL-Transpose-Rows-to-Columns.png",1280,720,false],"2048x2048":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/06\/Five.Co-MySQL-Transpose-Rows-to-Columns.png",1280,720,false]},"uagb_author_info":{"display_name":"Ryan Forrester","author_link":"https:\/\/preview-php85.dzine.org\/five\/author\/f1v5_ed9_au\/"},"uagb_comment_info":0,"uagb_excerpt":"There is always situations where information is spread across multiple rows, making analysis and reporting a challenge. This is where the concept of data transposition comes in. Transposing rows to columns essentially flips the table, organizing your data in a more column-oriented way. For example, if you have sales figures for different product categories across&hellip;","_links":{"self":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/3006","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/comments?post=3006"}],"version-history":[{"count":0,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/3006\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media\/3007"}],"wp:attachment":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media?parent=3006"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/categories?post=3006"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/tags?post=3006"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}