[MySQL] Pivot 쿼리.

컬럼과 로우를 위치 변경하는 pivot 쿼리 예시.

 

SELECT 
    CASE
        WHEN T_TEAM_SALES.salesUserId = 'L000000' THEN 'Others'
        WHEN T_TEAM_SALES.salesUserId = 'L999999' THEN 'Resigned'
        WHEN `sales_user`.`name` IS NULL THEN 'Others'
        ELSE `sales_user`.`name`
    END AS `salesUserName`,
    CASE
        WHEN T_TEAM_SALES.salesTeamId = 'T0000000000' THEN 'Others'
        WHEN `sales_team`.`name` IS NULL THEN 'Others'
        ELSE `sales_team`.`name`
    END AS `salesTeamName`,
    IFNULL(`customer_entity`.`customer_name`,
            'Others') AS `customerName`,
    T_TEAM_SALES.gross_1 AS `gross_1`,
    T_TEAM_SALES.net_1 AS `net_1`,
    T_TEAM_SALES.gross_2 AS `gross_2`,
    T_TEAM_SALES.net_2 AS `net_2`,
    T_TEAM_SALES.gross_3 AS `gross_3`,
    T_TEAM_SALES.net_3 AS `net_3`,
    T_TEAM_SALES.grossTotal AS `grossTotal`,
    T_TEAM_SALES.netTotal AS `netTotal`
FROM
    (SELECT 
        tblSub.SALES_TEAM_ID AS `salesTeamId`,
            tblSub.SALES_USER_ID AS `salesUserId`,
            tblSub.CUSTOMER_ID AS `customerId`,
            tblSub.ENTITY AS `Entity`,
            MAX(CASE
                WHEN tblSub.SALES_YM = '202004' THEN tblSub.gross_amount
                ELSE ''
            END) AS `gross_1`,
            MAX(CASE
                WHEN tblSub.SALES_YM = '202004' THEN tblSub.net_amount
                ELSE ''
            END) AS `net_1`,
            MAX(CASE
                WHEN tblSub.SALES_YM = '202005' THEN tblSub.gross_amount
                ELSE ''
            END) AS `gross_2`,
            MAX(CASE
                WHEN tblSub.SALES_YM = '202005' THEN tblSub.net_amount
                ELSE ''
            END) AS `net_2`,
            MAX(CASE
                WHEN tblSub.SALES_YM = '202006' THEN tblSub.gross_amount
                ELSE ''
            END) AS `gross_3`,
            MAX(CASE
                WHEN tblSub.SALES_YM = '202006' THEN tblSub.net_amount
                ELSE ''
            END) AS `net_3`,
            SUM(tblSub.gross_amount) AS `grossTotal`,
            SUM(tblSub.net_amount) AS `netTotal`
    FROM
        (SELECT 
        `aggregate_order`.`sales_quarter`,
            `aggregate_order`.`sales_ym`,
            `aggregate_order`.`sales_team_id`,
            `aggregate_order`.`sales_user_id`,
            `aggregate_order`.`customer_id`,
            `aggregate_order`.`entity`,
            SUM(`aggregate_order`.`gross_amount`) AS `gross_amount`,
            SUM(`aggregate_order`.`net_amount`) AS `net_amount`
    FROM
        `aggregate_order`
    WHERE
        (`aggregate_order`.`closing_date` = { d '2020-07-02' }
            AND `aggregate_order`.`sales_quarter` = '2020Q2'
            AND `aggregate_order`.`entity` = 'TWN')
    GROUP BY `aggregate_order`.`sales_quarter` , `aggregate_order`.`sales_ym` , `aggregate_order`.`sales_team_id` , `aggregate_order`.`sales_user_id` , `aggregate_order`.`allocated_customer_id`
    ORDER BY `aggregate_order`.`sales_team_id` , `aggregate_order`.`sales_user_id` , `aggregate_order`.`customer_id`) AS `tblSub`
    GROUP BY tblSub.SALES_TEAM_ID , tblSub.SALES_USER_ID , tblSub.CUSTOMER_ID) AS `T_TEAM_SALES`
        LEFT OUTER JOIN
    `sales_team` ON T_TEAM_SALES.salesTeamId = `sales_team`.`id`
        LEFT OUTER JOIN
    `sales_user` ON T_TEAM_SALES.salesUserId = `sales_user`.`id`
        LEFT OUTER JOIN
    `customer_line_entity` ON (T_TEAM_SALES.customerId = `customer_entity`.`customer_id`
        AND T_TEAM_SALES.entity = `customer_entity`.`line_entity`);