Woocommerce to Shop

gistfile1.txt

SELECT
        p.ID as order_id,
        p.post_date,
        max( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value END ) as Email,
        max( CASE WHEN pm.meta_key = '_shipping_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as 'First Name',
        max( CASE WHEN pm.meta_key = '_shipping_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as 'Last Name',
        max( CASE WHEN pm.meta_key = '_shipping_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as 'Default Address Address1',
        max( CASE WHEN pm.meta_key = '_shipping_address_2' and p.ID = pm.post_id THEN pm.meta_value END ) as 'Default Address Address2',
        max( CASE WHEN pm.meta_key = '_shipping_city' and p.ID = pm.post_id THEN pm.meta_value END ) as 'Default Address City',
        max( CASE WHEN pm.meta_key = '_shipping_state' and p.ID = pm.post_id THEN pm.meta_value END ) as 'Default Address Province Code',
        max( CASE WHEN pm.meta_key = '_shipping_country' and p.ID = pm.post_id THEN pm.meta_value END ) as 'Default Address Country Code',
        max( CASE WHEN pm.meta_key = '_shipping_postcode' and p.ID = pm.post_id THEN pm.meta_value END ) as 'Default Address Zip',
        max( CASE WHEN pm.meta_key = '_shipping_phone' and p.ID = pm.post_id THEN pm.meta_value END ) as 'Default Address Phone',
    
        ( select group_concat( order_item_name separator '|' ) from wp_woocommerce_order_items where 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
        post_type = 'shop_order' and
        post_date BETWEEN '2021-07-01' AND '2024-08-01' and
        post_status = 'wc-completed'
    group by
        p.ID;