MariaDB: PIVOT – Peter Lalovsky

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

Leave a comment Cancel reply