Magento

There may be a better way to write this, or do accomplish this kind of things, but this is at least a start. Feel free to comment about it here: http://www.magentocommerce.com/group/blog/action/viewpost/302/group/207/

Here is my select statement:

create view vi_sales_order_shipping as
select      so.increment_id as 'order_no',
        (    select xsov.value 
            from sales_order_varchar xsov 
            inner join eav_attribute xea on xsov.attribute_id = xea.attribute_id            where    xsov.entity_id = so.entity_id and 
                    xea.attribute_code = 'customer_email'
        ) as 'customer_email',



        /* --- SHIP TO ADDRESS --- */

        (   select xsoev.value 
            from sales_order_int soi_sa
            inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id and soi_sa_ea.attribute_code = 'shipping_address_id'
            inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where    so.entity_id = soi_sa.entity_id and
                    xea.attribute_code = 'firstname'
        ) as 'shipto_firstname',
        (   select xsoev.value 
            from sales_order_int soi_sa
            inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id and soi_sa_ea.attribute_code = 'shipping_address_id'
            inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where    so.entity_id = soi_sa.entity_id and
                    xea.attribute_code = 'lastname'
        ) as 'shipto_lastname',
        concat(
            (       select xsoev.value 
                from sales_order_int soi_sa
                inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id and soi_sa_ea.attribute_code = 'shipping_address_id'
                inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
                inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
                where    so.entity_id = soi_sa.entity_id and
                        xea.attribute_code = 'firstname'
            ), ' ', 
            (       select xsoev.value 
                from sales_order_int soi_sa
                inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id and soi_sa_ea.attribute_code = 'shipping_address_id'
                inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
                inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
                where    so.entity_id = soi_sa.entity_id and
                        xea.attribute_code = 'lastname'
            )
        ) as 'shipto_contact_name',
        (   select xsoev.value 
            from sales_order_int soi_sa
            inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id and soi_sa_ea.attribute_code = 'shipping_address_id'
            inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where    so.entity_id = soi_sa.entity_id and
                    xea.attribute_code = 'company'
        ) as 'shipto_company',
        (   select substring_index(xsoev.value, char(10), 1 ) 
            from sales_order_int soi_sa
            inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id and soi_sa_ea.attribute_code = 'shipping_address_id'
            inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where    so.entity_id = soi_sa.entity_id and
                    xea.attribute_code = 'street'
        ) as 'shipto_street_line_1',
        (   select case when xsoev.value like concat('%', char(10), '%') then substring_index(xsoev.value, char(10), -1 ) else '' end 
            from sales_order_int soi_sa
            inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id and soi_sa_ea.attribute_code = 'shipping_address_id'
            inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where    so.entity_id = soi_sa.entity_id and                    xea.attribute_code = 'street'
        ) as 'shipto_street_line_2',
        (   select xsoev.value 
            from sales_order_int soi_sa
            inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id and soi_sa_ea.attribute_code = 'shipping_address_id'
            inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where    so.entity_id = soi_sa.entity_id and                    xea.attribute_code = 'city'
        ) as 'shipto_city',
        (   select xsoev.value 
            from sales_order_int soi_sa
            inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id and soi_sa_ea.attribute_code = 'shipping_address_id'
            inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where    so.entity_id = soi_sa.entity_id and                    xea.attribute_code = 'region'
        ) as 'shipto_region',
        (   select xsoev.value 
            from sales_order_int soi_sa
            inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id and soi_sa_ea.attribute_code = 'shipping_address_id'
            inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where    so.entity_id = soi_sa.entity_id and
                    xea.attribute_code = 'postcode'
        ) as 'shipto_postcode',
        (   select xsoev.value 
            from sales_order_int soi_sa
            inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id and soi_sa_ea.attribute_code = 'shipping_address_id'
            inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where    so.entity_id = soi_sa.entity_id and
                    xea.attribute_code = 'country_id'
        ) as 'shipto_country_id',
        (    select xsoev.value 
            from sales_order_int soi_sa
            inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id and soi_sa_ea.attribute_code = 'shipping_address_id'
            inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where    so.entity_id = soi_sa.entity_id and
                    xea.attribute_code = 'telephone'
        ) as 'shipto_telephone',



        /* --- BILL TO ADDRESS --- */

        (   select xsoev.value 
            from sales_order_int soi_sa
            inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id and soi_sa_ea.attribute_code = 'billing_address_id'
            inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where    so.entity_id = soi_sa.entity_id and
                    xea.attribute_code = 'firstname'
        ) as 'billto_firstname',
        (   select xsoev.value 
            from sales_order_int soi_sa
            inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id and soi_sa_ea.attribute_code = 'billing_address_id'
            inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where    so.entity_id = soi_sa.entity_id and
                    xea.attribute_code = 'lastname'
        ) as 'billto_lastname',
        concat(
            (       select xsoev.value 
                from sales_order_int soi_sa
                inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id and soi_sa_ea.attribute_code = 'billing_address_id'
                inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
                inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
                where    so.entity_id = soi_sa.entity_id and
                        xea.attribute_code = 'firstname'
            ), ' ', 
            (       select xsoev.value 
                from sales_order_int soi_sa
                inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id and soi_sa_ea.attribute_code = 'billing_address_id'
                inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
                inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
                where    so.entity_id = soi_sa.entity_id and
                        xea.attribute_code = 'lastname'
            )
        ) as 'billto_contact_name',
        (   select xsoev.value 
            from sales_order_int soi_sa
            inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id and soi_sa_ea.attribute_code = 'billing_address_id'
            inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where    so.entity_id = soi_sa.entity_id and
                    xea.attribute_code = 'company'
        ) as 'billto_company',
        (   select substring_index(xsoev.value, char(10), 1 ) 
            from sales_order_int soi_sa
            inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id and soi_sa_ea.attribute_code = 'billing_address_id'
            inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where    so.entity_id = soi_sa.entity_id and
                    xea.attribute_code = 'street'
        ) as 'billto_street_line_1',
        (   select case when xsoev.value like concat('%', char(10), '%') then substring_index(xsoev.value, char(10), -1 ) else '' end 
            from sales_order_int soi_sa
            inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id and soi_sa_ea.attribute_code = 'billing_address_id'
            inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where    so.entity_id = soi_sa.entity_id and                    xea.attribute_code = 'street'
        ) as 'billto_street_line_2',
        (   select xsoev.value 
            from sales_order_int soi_sa
            inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id and soi_sa_ea.attribute_code = 'billing_address_id'
            inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where    so.entity_id = soi_sa.entity_id and                    xea.attribute_code = 'city'
        ) as 'billto_city',
        (   select xsoev.value 
            from sales_order_int soi_sa
            inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id and soi_sa_ea.attribute_code = 'billing_address_id'
            inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where    so.entity_id = soi_sa.entity_id and                    xea.attribute_code = 'region'
        ) as 'billto_region',
        (   select xsoev.value 
            from sales_order_int soi_sa
            inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id and soi_sa_ea.attribute_code = 'billing_address_id'
            inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where    so.entity_id = soi_sa.entity_id and
                    xea.attribute_code = 'postcode'
        ) as 'billto_postcode',
        (   select xsoev.value 
            from sales_order_int soi_sa
            inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id and soi_sa_ea.attribute_code = 'billing_address_id'
            inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where    so.entity_id = soi_sa.entity_id and
                    xea.attribute_code = 'country_id'
        ) as 'billto_country_id',
        (    select xsoev.value 
            from sales_order_int soi_sa
            inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id and soi_sa_ea.attribute_code = 'billing_address_id'
            inner join sales_order_entity_varchar xsoev on xsoev.entity_id = soi_sa.value
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where    so.entity_id = soi_sa.entity_id and
                    xea.attribute_code = 'telephone'
        ) as 'billto_telephone',



        /* --- ORDER DETAILS --- */

        (   select xsod.value 
            from sales_order_decimal xsod
            inner join eav_attribute xea on xsod.attribute_id = xea.attribute_id
            where    xsod.entity_id = so.entity_id and 
                    xea.attribute_code = 'weight'
        ) as 'order_weight',
        so.subtotal as 'order_subtotal',
        so.grand_total as 'order_total'
from sales_order so
/*where so.increment_id = '100000412';*/
/*use the above to filter by a specific order number*/

After you have the view created then you can treat the view similar to how you treat a table.

select *
from vi_sales_order_shipping

or if you want to reference a single order

select *
from vi_sales_order_shipping
where order_no = '100000004'