Member Avatar for bumbumpaw 
Create Procedure `SelectProduct` (IN prod VARCHAR(70)) 
BEGIN

SET @sql = null;

SELECT GROUP_CONCAT(DISTINCT CONCAT( 'max(case when Attribute = ''',Attribute,'" Then p.Attributevalue end) as `',Attribute,'`' )  
INTO @sql FROM tblAttribute;

SET @sql = CONCAT('Select pr.product, ',@sql,' from tblproduct tblproduct pr LEFT JOIN tblproductattribute p using(productid) left join tblattribute a using (attributeid)
where pr.product LIKE (''%',prod,'%'') group by pr.productid ');
Prepare st from @sql;
execute stmt; 
END

Please help me,the procedure about output columns like this

|Product|Width|Color|Size|

what i need is like this

|Product|Product description|
sampleproduct|13mm,Green,Small
Edited by pritaeas because: Fixed markdown.
  • 2 Contributors
  • 1 Reply
  • 294 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by LastMitch
Member Avatar for LastMitch

what i need is like this

@bumbumpaw

I'm not sure if anyone will be able to create a query like that?

If your table structure is like this:

|Product|Width|Color|Size|

but you want it to group:

|Product|Product description|

Can you be specific why you need to group that like? On many e-comm platform, they have their own are mysql query to do that, it should be in the guideline.

If you want to echo out the data from the front to look like this:

|Product|Product description|
sampleproduct|13mm,Green,Small

Then create it I don't see any issue with that but if your intention to group your data or reorganized the table structure is a bit hard, it involve more than just a query.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.