Dynamic Pivot with Prepared Statements
SET @sql = NULL;
SELECT GROUP_CONCAT(
CONCAT(
'MAX(CASE WHEN product_name = ''',
product_name,
''' THEN sales_quantity ELSE NULL END) AS ',
product_name, '_Sales'
)
) INTO @sql
FROM (
SELECT DISTINCT product_name FROM SalesData
) t;
SET @sql = CONCAT('SELECT ', @sql, ' FROM SalesData;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Output:
Explanation: In the above dynamicQuery, the statement prepares and executes a query to pivot the SalesData table. It first generates a list of columns using the DISTINCT product names, then constructs a SELECT statement with MAX(CASE WHEN…) for each product to pivot the data. The final SELECT statement is executed to display the pivoted data.
How to Efficiently Convert Rows to Columns in MariaDB?
In the area of database management, the ability to convert rows to columns efficiently is a valuable skill. MariaDB, a popular open-source relational database management system offers various methods to achieve this transformation.
In this article, we’ll learn about How to Convert Rows into Columns in MariaDB with the help of various methods along with examples and so on.
Contact Us