SELECT `p`.`id`                               AS `order_id`,
       Cast(`p`.`post_date` AS datetime)      AS `order_date`,
       SUBSTRING(`p`.`post_status`, 4)        AS `order_status`,
       Max(CASE WHEN `pm`.`meta_key` = '_billing_email' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value`  end)                                 AS `billing_email`,
       Max(CASE WHEN `pm`.`meta_key` = '_billing_first_name' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value`  end)                            AS `_billing_first_name`,
       Max(CASE WHEN `pm`.`meta_key` = '_billing_last_name' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value`  end)                             AS `_billing_last_name`,
       Max(CASE WHEN `pm`.`meta_key` = '_billing_company' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value`  end)                               AS `_billing_company`,       
       Max(CASE WHEN `pm`.`meta_key` = '_billing_address_1' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value`  end)                             AS `_billing_address_1`,
       Max(CASE WHEN `pm`.`meta_key` = '_billing_address_2' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value`  end)                             AS `_billing_address_2`,
       Max(CASE WHEN `pm`.`meta_key` = '_billing_city' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value`  end)                                  AS `_billing_city`,
       Max(CASE WHEN `pm`.`meta_key` = '_billing_state' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value`  end)                                 AS `_billing_state`,
       Max(CASE WHEN `pm`.`meta_key` = '_billing_country' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value`  end)                               AS `_billing_country`,       
       Max(CASE WHEN `pm`.`meta_key` = '_billing_postcode' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value`  end)                              AS `_billing_postcode`,
       Max(CASE WHEN `pm`.`meta_key` = '_shipping_first_name' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value`  end)                           AS `_shipping_first_name`,
       Max(CASE WHEN `pm`.`meta_key` = '_shipping_last_name' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value`  end)                            AS `_shipping_last_name`,
       Max(CASE WHEN `pm`.`meta_key` = '_shipping_company' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value`  end)                              AS `_shipping_company`,        
       Max(CASE WHEN `pm`.`meta_key` = '_shipping_address_1' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value`  end)                            AS `_shipping_address_1`,
       Max(CASE WHEN `pm`.`meta_key` = '_shipping_address_2' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value`  end)                            AS `_shipping_address_2`,
       Max(CASE WHEN `pm`.`meta_key` = '_shipping_city' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value`  end)                                 AS `_shipping_city`,
       Max(CASE WHEN `pm`.`meta_key` = '_shipping_state' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value`  end)                                AS `_shipping_state`,
       Max(CASE WHEN `pm`.`meta_key` = '_shipping_country' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value`  end)                              AS `_shipping_country`,       
       Max(CASE WHEN `pm`.`meta_key` = '_shipping_postcode' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value`  end)                             AS `_shipping_postcode`,
       Max(CASE WHEN `pm`.`meta_key` = '_payment_method_title' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value`  end)                          AS `_payment_method_title`,
       Cast(Max(CASE WHEN `pm`.`meta_key` = '_order_total' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value`  end) AS DECIMAL(16, 2))           AS `order_total`,
       Cast(Max(CASE WHEN `pm`.`meta_key` = '_order_tax' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value`  end) AS DECIMAL(16, 2))             AS `order_tax`,
       Cast(Max(CASE WHEN `pm`.`meta_key` = '_order_shipping' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value`  end) AS DECIMAL(16, 2))        AS `_order_shipping`,
       Cast(Max(CASE WHEN `pm`.`meta_key` = '_order_shipping_tax' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value`  end) AS DECIMAL(16, 2))    AS `_order_shipping_tax`,
       Cast(Max(CASE WHEN `pm`.`meta_key` = '_paid_date' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value`  end) AS datetime)                   AS `paid_date`,
       (SELECT Group_concat(`wp_woocommerce_order_items`.`order_item_name` SEPARATOR '|')
        FROM   `wp_woocommerce_order_items`
        WHERE  `wp_woocommerce_order_items`.`order_id` = `p`.`id`) AS `order_items`
FROM   ((`wp_posts` `p`
         JOIN `wp_postmeta` `pm`
           ON( `p`.`id` = `pm`.`post_id` ))
        JOIN `wp_woocommerce_order_items` `oi`
          ON( `p`.`id` = `oi`.`order_id` ))
WHERE  `p`.`post_type` = 'shop_order'
GROUP  BY `p`.`id`