Aggregate rows inside new columns-CodePudding

I would like to join the two first tables (Product and ProductProperties) to get the result in the bottom.

How do I do this?

CodePudding user response：

There are penalties for EAV. I'm not saying EAV is evil, but should be deployed carefully and with great forethought.

Here are two examples. The first is a PIVOT, and the second is a conditional aggregation.

I tend to lean towards the conditional aggregation, it offers more flexibility and often a performance bump

Untested for you did not supply sample data and desired results as text

Select *
 From  (
         Select A.product_id
               ,A.product_name
               ,B.product_property
               ,B.product_property_value
          From  Product A
          Join ProductProperties B on A.product_id=B.product_di
       ) src
 Pivot (max( product_property_value ) for product_property in ([Price],[Category],[Status] ) ) pvt

Select A.product_id
      ,A.product_name
      ,Price    = max( case when product_propery='Price'    then product_propery_value end)
      ,Category = max( case when product_propery='Category' then product_propery_value end)
      ,Status   = max( case when product_propery='Status'   then product_propery_value end)
 From  Product A
 Join  ProductProperties B on A.product_id=B.product_di
 Group By A.product_id,A.product_name

CodePudding user response：

SELECT
b.product_id
,b.product_name
,Price = MAX(IIF(p.product_property = 'Price',p.product_property_value,NULL))
,Category = MAX(IIF(p.product_property = 'Category',p.product_property_value,NULL))
,Status = MAX(IIF(p.product_property = 'Status',p.product_property_value,NULL))
FROM books b (nolock)
JOIN prodprop p (nolock)
ON b.product_id = p.product_id
GROUP BY b.product_id,b.product_name