How to pivot a MySQL entity-attribute-value schema

by

The question mentions MySQL, and in fact this DBMS has a special function for this kind of problem: GROUP_CONCAT(expr). Take a look in the MySQL reference manual on group-by-functions. The function was added in MySQL version 4.1. You’ll be using GROUP BY FileID in the query.

I’m not really sure about how you want the result to look. If you want every attribute listed for every item (even if not set), it will be harder. However, this is my suggestion for how to do it:

SELECT bt.FileID, Title, Author, 
 GROUP_CONCAT(
  CONCAT_WS(':', at.AttributeName, at.AttributeType, avt.AttributeValue) 
  ORDER BY at.AttributeName SEPARATOR ', ') 
FROM BaseTable bt JOIN AttributeValueTable avt ON avt.FileID=bt.FileID 
 JOIN AttributeTable at ON avt.AttributeId=at.AttributeId 
GROUP BY bt.FileID;

This gives you all attributes in the same order, which could be useful. The output will be like the following:

'F001', 'Dox', 'vinay', 'CustomAttr1:varchar(40):Akash, CustomUseDate:Datetime:2009/03/02'

This way you only need one single DB query, and the output is easy to parse. If you want to store the attributes as real Datetime etc. in the DB, you’d need to use dynamic SQL, but I’d stay clear from that and store the values in varchars.

More Related Contents:

  1. Entity-Attribute-Value Table Design
  2. Modeling Product Variants
  3. How can I return pivot table output in MySQL?
  4. MySQL pivot row into dynamic number of columns
  5. MySQL pivot table query with dynamic columns
  6. What does character set and collation mean exactly?
  7. Implementing Comments and Likes in database
  8. Mysql query to dynamically convert rows to columns
  9. mysql pivot/crosstab query
  10. MySQL – Conditional Foreign Key Constraints
  11. MySQL dynamic-pivot
  12. MySQL stored procedures use them or not to use them
  13. In SQL, is it OK for two tables to refer to each other?
  14. MySQL ENUM type vs join tables
  15. Mysql Convert Column to row (Pivot table )
  16. Mysql query to dynamically convert rows to columns on the basis of two columns
  17. Remove Primary Key in MySQL
  18. Setting column values as column names in the SQL query result
  19. mysql pivot query results with GROUP BY
  20. DOs and DONTs for Indexes [closed]
  21. MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  22. MySQL – Supertype/Subtype design
  23. Difference between two table structure
  24. Strings as Primary Keys in MYSQL Database [closed]
  25. When to use a left outer join?
  26. Join two tables (with a 1-M relationship) where the second table needs to be ‘flattened’ into one row
  27. MySQL: how to do row-level security (like Oracle’s Virtual Private Database)?
  28. List of Constraints from MySQL Database
  29. MySQL Relationships
  30. Inventory management with stock options

Leave a Comment