report_master_product

CREATE view report_master_product as
select
    cpvfd.variation_id as 'Variation Id',
    cpvfd.product_id as 'Product Id',
    cpfd.`type` as 'Type',
    c.name as 'Brand',
    cpvfd.title as 'Title',
    sub.category as 'Category',
    principal.name as 'Principal',
    if(cpvfd.status, 'Published', 'Unpublished') as 'Status',
    cpvdimensions.dimensions_length as 'Dimension Length',
    cpvdimensions.dimensions_width as 'Dimension Width',
    cpvdimensions.dimensions_height as 'Dimension Height',
    cpvdimensions.dimensions_unit as 'Dimension Unit',
    cpvweight.weight_number as 'Weight',
    cpvweight.weight_unit as 'Weight Unit',
    cpavfd.name as 'UOM'
from commerce_product_variation_field_data cpvfd
left join commerce_product_field_data cpfd
on cpvfd.product_id = cpfd.product_id
left join taxonomy_term_field_data c
on cpfd.brand = c.tid and c.vid = 'brand'
left join commerce_product_variation__dimensions cpvdimensions
on cpvfd.variation_id = cpvdimensions.entity_id
left join commerce_product_variation__weight cpvweight
on cpvfd.variation_id = cpvweight.entity_id
left join commerce_product_variation__attribute_uom cpvuom
on cpvfd.variation_id = cpvuom.entity_id
left join commerce_product_attribute_value_field_data cpavfd
on cpvuom.attribute_uom_target_id = cpavfd.attribute_value_id
left join (select
        cpc.entity_id,
        group_concat(DISTINCT ttfdcategory.name) as 'category'
    from commerce_product__categories cpc
    left join taxonomy_term_field_data ttfdcategory
    on ttfdcategory.tid = cpc.categories_target_id and ttfdcategory.vid = 'product_category'
    group by cpc.entity_id
) sub
on cpvfd.product_id = sub.entity_id
left join (select
        ttp.entity_id,
        ttfdcategory.name
    from taxonomy_term__principal ttp
    left join taxonomy_term_field_data ttfdcategory
    on ttfdcategory.tid = ttp.principal_target_id and ttfdcategory.vid = 'principal'
) principal
on cpfd.brand = principal.entity_id