SQL Select Query for Retrieving WooCommerce Order Items and Meta Data
<p>I'm working on creating some SQL Views for my Wordpress database to more easily view online orders generated via the WooCommerce plugin. I'm familiar with the WooCommerce structure and where the data is stored but being an SQL novice I'm getting stuck at the last hurdle.</p>

<p>I've been able to create the first view of all the order details using this reference:</p>

<p><a href="http://codecharismatic.com/sql-script-to-get-all-woocommerce-orders-including-metadata/" rel="noreferrer">http://codecharismatic.com/sql-script-to-get-all-woocommerce-orders-including-metadata/</a></p>

<p>Now I need to create a similar view of the line items for each of these orders. There are 2 tables that store order item details:</p>

<p>wp_woocommerce_order_items
wp_woocommerce_order_itemmeta</p>

<p>I've been able to create the basic list of records from the wp_woocommerce_order_items table as follows:</p>

<pre><code>SELECT order_item_id,order_item_name,order_item_type,order_id
FROM ocm_woocommerce_order_items 
ORDER BY order_id
</code></pre>

<p>This returns a nice list of records like this:</p>

<pre><code>order_item_id   order_item_name order_item_type order_id
 2  Widgets line_item   9
 3  Widgets line_item   10
 4  Widgets line_item   11
 5  Woo Logo    line_item   473
 6  Woo Logo    line_item   473
</code></pre>

<p>I would now like to add additional columns for each line item from the wp_woocommerce_order_itemmeta table. This table contains multiple records for each line item like this:</p>

<pre><code> meta_id    order_item_id   meta_key    meta_value
 136    16  _qty    4
 137    16  _tax_class  
 138    16  _product_id 87
 139    16  _variation_id   0
 140    16  _line_subtotal  36
 141    16  _line_total 36
 142    16  _line_subtotal_tax  3.6
 143    16  _line_tax   3.6
</code></pre>

<p>I would like to convert each of these into a new column so my output would consist of:</p>

<pre><code> order_item_id, order_item_name, order_item_type, order_id, qty, tax_class, product_id, variation_id, line_subtotal, line_total, line_subtotal_tax, line_tax
</code></pre>

<p>I'm not sure which SQL syntax to use here - I've tried to use a similar syntax to the link above but haven't been able to get it to work so far and not sure if that is indeed the correct syntax.</p>