Created
June 13, 2024 04:19
-
-
Save corporatepiyush/6d4388a50283f24fdd6f72688a5160f2 to your computer and use it in GitHub Desktop.
Dynamic Pivot for SQL result sets
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE PROCEDURE GenericPivot( | |
IN tableName VARCHAR(255), | |
IN pivotColumn VARCHAR(255), | |
IN pivotValues VARCHAR(255), | |
IN aggregateColumn VARCHAR(255), | |
IN groupByColumn VARCHAR(255) | |
) | |
BEGIN | |
DECLARE columnsList TEXT; | |
DECLARE sql_query TEXT; | |
-- Get the distinct list of pivot values | |
SELECT GROUP_CONCAT(DISTINCT | |
CONCAT('SUM(CASE WHEN ', pivotColumn, ' = ''', value, ''' THEN ', aggregateColumn, ' ELSE 0 END) AS `', value, '`') | |
) INTO columnsList | |
FROM (SELECT DISTINCT value FROM (SELECT DISTINCT REPLACE(pivotValues, ',', ' ') AS value) AS temp) AS pivotVals; | |
-- Construct the final SQL query | |
SET sql_query = CONCAT('SELECT ', groupByColumn, ', ', columnsList, ' FROM ', tableName, ' GROUP BY ', groupByColumn); | |
-- Prepare and execute the query | |
PREPARE stmt FROM sql_query; | |
EXECUTE stmt; | |
DEALLOCATE PREPARE stmt; | |
END |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment