MySQL script to get all WooCommerce orders including metadata

Query

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 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_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_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_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_postcode' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_postcode,
    max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
    max( CASE WHEN pm.meta_key = '_order_tax' and p.ID = pm.post_id THEN pm.meta_value END ) as order_tax,
    max( CASE WHEN pm.meta_key = '_paid_date' and p.ID = pm.post_id THEN pm.meta_value END ) as paid_date,
    ( 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-01-01' AND '2021-08-01' and
    post_status = 'wc-completed'
group by
    p.ID

@Flioper & @iputuadi want to maybe post what you have so far so one can assist?

I want to get the value of the quantity in each order. I try to use the "_qty" parameter, but it doesn't show anything. Do you know how to get the quantity value for each order on woocommerce via a query?

With qty was a little harder, here i have this code, for me its works, shows everything :) (select group_concat(woim.meta_value separator ' ' ) from wp_woocommerce_order_items woi join wp_woocommerce_order_itemmeta woim on woi.order_item_id = woim.order_item_id where woim.meta_key = '_qty' and woi.order_id = oi.order_id) as qty,

Yes, this works.
Thanks a lot.

Hello,
May be i'm doing something wrong, but I have this error message : #1146 - Table 'greentrepxtrek.wp_woocommerce_order_items' doesn't exist

Can someone help me please ?

Hello, May be i'm doing something wrong, but I have this error message : #1146 - Table 'greentrepxtrek.wp_woocommerce_order_items' doesn't exist

Can someone help me please ?

@Aziz-Abdelli you will need to check your database for that missing table, WooCommerce usually needs that table, so it shouldn't be missing. If you logged into your store and went to WooCommerce -> Status -> Tools -> Verify base database tables this might assist you.