1

I have following problem: My data is ungrouped in the form of

productid category attribute attributvalue
product1 cat A length 20cm
product2 cat A length 40cm
product3 cat A width 20cm
product4 cat B length 30cm

I want to have the data kind of grouped while concating the records of the values like

category length width height attribute x y z
cat A 20cm, 40cm 20cm ... ... ... ...
cat B 30cm

Can somebody pls help me here? Thx a lot

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
Tschou99
  • 13
  • 3
  • You need to unpivot data then to use [MySQL GROUP_CONCAT Function](https://www.mysqltutorial.org/mysql-group_concat/) – Maciej Los Jul 07 '21 at 08:59
  • @MaciejLos Backward. OP needs to pivot his data (after its aggregation, of course). – Akina Jul 07 '21 at 09:26
  • Agree. But firstly OP have to unpivot data to be able to pivot them. – Maciej Los Jul 07 '21 at 09:28
  • Are you using MySQL or proc SQL? These are very different! Are there ever more than two values for an attribute in a category? – Gordon Linoff Jul 07 '21 at 10:30
  • Hi im using proc sql, but can connect from SAS to an oracle datawarehouse and also execute sql there. yes there are many attributes for each category and those attributes have many different values, which i wanna have concatted. – Tschou99 Jul 07 '21 at 11:02

1 Answers1

0

Look at the data you have. You repeat the name of the attribute, which violates the third normal form, as it's an unnecessary data redundancy. You will need to refactor your database structure. Create the following tables:

  • attribute (id, attribute_name)
  • product_attribute (id, attribute_id, value)

Modify your table so it has this structure: product (id, category, product_attribute_id)

and then you can join product - product_attribute - attribute and group by category, using group_concat for the attributes.

How to get the columns? Simple:

SELECT attribute_name
FROM attributes;

Let's generate some stuff form this:

SELECT CONCAT('CASE WHEN attribute_name = \'', attribute.attribute_name, '\' THEN attribute.attribute_name ELSE NULL END AS \'', attribute.attribute_name, '\'')
FROM attribute;

So, you will need to use a cursor in order to iterate the results and that will be a SELECT clause that you generate. Let's see how the text to be generated should look alike:

/*SELECT CLAUSE HERE BASED ON THE INSTRUCTIONS ABOVE*/
FROM attribute
JOIN product_attribute
ON attribute.id = product_attribute.attribute_id
JOIN product
ON product_attribute_id = product.product_attribute_id
GROUP BY category;

and then EXECUTE this text. Yes, it is not straight-forward and yes, you will need to restructure your database, normalize your tables and migrate your data as a prerequisite for this solution, but INMHO keeping your database not normalized would be harmful by itself.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175