Pivot table in mysql

MS-SQL에서는 PIVOT 키워드를 이용해서 쉽게 피봇 테이블을 할 수 있지만,
mysql 에서는 해당 키워드가 없습니다.

다음과 같이 저장프로시저를 만들어서 테이블을 피봇할 수 있습니다.

DROP PROCEDURE IF EXISTS `MyPivot`;
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `MyPivot`(
    IN tbl_name TEXT,       
    IN group_by_func VARCHAR(99),   
    IN base_cols VARCHAR(99),      
    IN pivot_col VARCHAR(64),      
    IN pivot_col_order_by VARCHAR(99),   
    IN tally_col VARCHAR(64),    
    IN where_clause TEXT,   
    IN order_by VARCHAR(99)        
    )
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    /*
        <parameter>

         -- table name (or db.tbl)
         -- using function after <group by> (SUM, COUNT, GROUP_CONCAT, ...)
         -- column(s) on the left, separated by commas
         -- name of column to put across the top
         -- empty string or "BY order_idx ASC, ex_idx ASC, ..."; order by pivoting columns  
         -- name of column to SUM up
         -- empty string or "WHERE ..."
         -- empty string or "ORDER BY ..."; usually the base_cols     
     */

    -- Find the distinct values
    -- Build the SUM()s
    SET @pcob = IF(pivot_col_order_by='', '1', pivot_col_order_by);

    SET @subq = CONCAT('SELECT DISTINCT ', pivot_col, ' AS val ', ' FROM ', tbl_name, ' ', where_clause, ' ORDER BY ', @pcob);                    
    -- select @subq;

    -- 숫자를 다룰 때만 SUM을 추가.
    SET @sum_total = '\n';
    SET @elseval = 'NULL';
    IF group_by_func IN ('SUM', 'COUNT') THEN
        SET @sum_total = CONCAT(',\n SUM(', tally_col, ') AS Total');
        SET @elseval = '0';
    END IF;

    SET @cc0 = "CONCAT('&gbf(IF(&p = ', &v, ', &t, &e)) AS ', &v)";
    SET @cc1 = REPLACE(@cc0, '&e', @elseval);
    SET @cc2 = REPLACE(@cc1, '&p', pivot_col);
    SET @cc3 = REPLACE(@cc2, '&t', tally_col);
    -- select @cc2, @cc3;
    SET @qval = CONCAT("'\"', val, '\"'");
    -- select @qval;
    SET @cc4 = REPLACE(@cc3, '&v', @qval);
    -- select @cc4;
    SET @cc5 = REPLACE(@cc4, '&gbf', group_by_func);
    -- select @cc5;

    SET SESSION group_concat_max_len = 10000;   -- just in case
    SET @stmt = CONCAT(
            'SELECT  GROUP_CONCAT(', @cc5, ' SEPARATOR ",\n")  INTO @sums',
            ' FROM ( ', @subq, ' ) AS top');
     select @stmt;
    PREPARE _sql FROM @stmt;
    EXECUTE _sql;                      -- Intermediate step: build SQL for columns
    DEALLOCATE PREPARE _sql;
    -- Construct the query and perform it

    SET @stmt2 = CONCAT(
            'SELECT ',
                base_cols, ',\n',
                @sums,
                @sum_total, 
            '\n FROM ', tbl_name, ' ',
            where_clause,
            ' GROUP BY ', base_cols,
            -- '\n WITH ROLLUP',
            '\n', order_by
        );
    select @stmt2;                    -- The statement that generates the result
    PREPARE _sql FROM @stmt2;
    EXECUTE _sql;                     -- The resulting pivot table ouput
    DEALLOCATE PREPARE _sql;
    -- For debugging / tweaking, SELECT the various @variables after CALLing.
END//
DELIMITER ;

프로시저 호출 예시

/* 설문자 별 전체 답변 내용 - (교사) */
CALL MyPivot(
    'betea.t_temp_member_answer',
    'GROUP_CONCAT',
    'member_idx',    -- group by
    'question_text', -- column
    'order_idx ASC', -- columns ordering option
    'member_answer', -- grouping value
    'WHERE rec_uf_g_idx = 27 and member_idx > 0',
    '');

/* 설문자 별 전체 보기 선택 내용 - (교사) */
/* 클러스터링 시 벡터 데이터로 활용 */
CALL MyPivot(
    'betea.t_temp_member_answer',
    'COUNT',
    'member_idx',    -- group by
    'rec_uf_q_ex_idx', -- column
    'order_idx ASC, ex_idx ASC',
    1, -- column value
    'WHERE rec_uf_g_idx = 27 AND question_type > -1', -- 주관식 제외
    '');