I googled “mariadb pivot” and the first answer was this. I need to generate this code:
SELECT MAX(H.col1) AS col1 , MAX(H.col2) AS col2 , MAX(H.col3) AS col3 FROM ( SELECT 'A' AS A, 'This is Col 1' AS col1, NULL AS col2, NULL AS col3 UNION ALL SELECT 'A' AS A, NULL AS col1, 'This is Col 2' AS col2, NULL AS col3 UNION ALL SELECT 'A' AS A, NULL AS col1, NULL AS col2, 'This is Col 3' AS col3 ) AS H GROUP BY H.A;
In few words PIVOT is grouping by two groups:
- CustomerID, ItemID
- Period

And the result is:

I played a little and i got this:
DELIMITER | BEGIN NOT ATOMIC #-------------------------------------------------- /* TODO: * Put this in dynamic code to use the commented variables * Suppress the output from the statements that append to @dynamic_select_code and @dynamic_from_code * Create procedure */ # Procedure Parameters SET @aggregation_type = 'AVG'; # SUM, AVG, MIN, MAX #SET @table_name = 'pl_temp'; -- The table that will be pivoted #SET @rows = 'col;col2;col3'; -- The columns that will be represented as rows #SET @columns = 'co4;'; -- The columns that will be represented as columns #SET @values = 'val'; -- The aggregated column #SET @where = 'val'; -- WHERE clause #SET @order_by = 'val'; -- ORDER BY clause # Temp table - Start DROP TEMPORARY TABLE IF EXISTS pl_temp; CREATE TEMPORARY TABLE pl_temp (id INT AUTO_INCREMENT PRIMARY KEY, col VARCHAR(16), val VARCHAR(32)); INSERT INTO pl_temp (col, val) SELECT 'zulu', '11.735146' UNION ALL SELECT 'zulu', '12' UNION ALL SELECT 'ali', '31' UNION ALL SELECT 'ali', '32.215634' UNION ALL SELECT 'ali', '33' UNION ALL SELECT 'ali', '34.75657' UNION ALL SELECT 'nemo', '21.13270' UNION ALL SELECT 'nemo', '22' UNION ALL SELECT 'nemo', '23.435'; # Temp table - End # Generate SELECT - Start SET @dynamic_select_code := 'SELECT'; SELECT @dynamic_select_code := CONCAT(@dynamic_select_code , CASE WHEN A.rn = 1 THEN CONCAT(' MAX(U.`', col, '`) AS `', col, '`') ELSE CONCAT(' , MAX(U.`', col, '`) AS `', col, '`') END) FROM ( SELECT ROW_NUMBER() OVER() AS rn , col FROM pl_temp GROUP BY col ORDER BY id ) AS A; SET @dynamic_select_code = CONCAT(@dynamic_select_code, ' FROM (' ); # Generate SELECT - End # Generate FROM - Start SET @dynamic_from_code = ''; SELECT @dynamic_from_code := CONCAT(@dynamic_from_code, H.stmt) FROM ( SELECT ROW_NUMBER() OVER(ORDER BY A.rn, T.rn) AS rn , CONCAT(CASE WHEN (T.rn = 1 AND A.rn = 1) THEN ' SELECT ''A'' AS `A`' WHEN (T.rn = 1 AND A.rn != 1) THEN ' UNION ALL SELECT ''A'' AS `A`' ELSE ' ' END , CASE WHEN A.rn = T.rn THEN CONCAT(', ', A.aggregated, ' AS `', A.col, '`') ELSE CONCAT(', NULL AS `', T.col, '`') END) AS stmt FROM ( SELECT ROW_NUMBER() OVER() AS rn , col , CASE WHEN @aggregation_type = 'SUM' THEN SUM(val) WHEN @aggregation_type = 'AVG' THEN AVG(val) WHEN @aggregation_type = 'MIN' THEN MIN(val) WHEN @aggregation_type = 'MAX' THEN MAX(val) END AS aggregated FROM pl_temp GROUP BY col ORDER BY id ) AS A JOIN ( SELECT ROW_NUMBER() OVER() AS rn , col FROM pl_temp GROUP BY col ORDER BY id ) AS T ) AS H ORDER BY H.rn; SET @dynamic_from_code = CONCAT(@dynamic_from_code, ' ) AS U GROUP BY U.A; '); # Generate FROM - End # Execute the dynamic code EXECUTE IMMEDIATE CONCAT(@dynamic_select_code, @dynamic_from_code); #-------------------------------------------------- END | DELIMITER ;
The result is:

The same code for SQL Server:
USE PL; SET NOCOUNT ON; DROP TABLE IF EXISTS #pl_temp; CREATE TABLE #pl_temp (id INT IDENTITY(1, 1) PRIMARY KEY, col VARCHAR(16), val DECIMAL(18, 6) /* Numeric data type */); INSERT #pl_temp (col, val) SELECT 'zulu', '11.735146' UNION ALL SELECT 'zulu', '12' UNION ALL SELECT 'ali', '31' UNION ALL SELECT 'ali', '32.215634' UNION ALL SELECT 'ali', '33' UNION ALL SELECT 'ali', '34.75657' UNION ALL SELECT 'nemo', '21.13270' UNION ALL SELECT 'nemo', '22' UNION ALL SELECT 'nemo', '23.435'; DECLARE @aggregation CHAR(3) = 'AVG' -- SUM, AVG, MIN, MAX , @DynamicSQL VARCHAR(MAX) = 'SELECT'; --** Generate SELECT - Start **-- SELECT @DynamicSQL += CASE WHEN A.id = 1 THEN CONCAT(' MAX(H.', QUOTENAME(col), ') AS ', QUOTENAME(col)) ELSE CONCAT(' , MAX(H.', QUOTENAME(col), ') AS ', QUOTENAME(col)) END FROM ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS id , col FROM #pl_temp GROUP BY col ) AS A; SELECT @DynamicSQL += ' FROM (' --** Generate SELECT - End **-- --** Generate FROM - Start **-- SELECT @DynamicSQL += CONCAT( CASE WHEN (T.id = 1 AND A.row_num = 1) THEN ' SELECT ''A'' AS [A]' WHEN (T.id = 1 AND A.row_num != 1) THEN ' UNION ALL SELECT ''A'' AS [A]' END , CASE WHEN A.row_num = T.id THEN CONCAT(', ', A.aggregated, ' AS ', QUOTENAME(A.col)) ELSE CONCAT(', NULL AS ', QUOTENAME(T.col)) END ) FROM ( SELECT col , CASE WHEN @aggregation = 'SUM' THEN SUM(val) --WHEN @aggregation = 'AVG' THEN AVG(CAST(val AS FLOAT)) -- FLOAT? WHEN @aggregation = 'AVG' THEN AVG(val) -- FLOAT? WHEN @aggregation = 'MIN' THEN MIN(val) WHEN @aggregation = 'MAX' THEN MAX(val) END AS aggregated , ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS row_num FROM #pl_temp GROUP BY col ) AS A CROSS APPLY ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS id , col FROM #pl_temp GROUP BY col ) AS T; --** Generate FROM - End **-- SELECT @DynamicSQL += ' ) AS H GROUP BY H.A; '; EXEC (@DynamicSQL);

It looks like my code is more complex and stupid than the original one :-)
Keep it simple :-)