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 :-)
