How to pivot a MySQL entity-attribute-value schema

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.

If you're looking for something more usable (and joinable) than a group-concat result, try this solution below. I've created some tables very similar to your example to make this make sense.

This works when:

• You want a pure SQL solution (no code, no loops)
• You have a predictable set of attributes (e.g. not dynamic)
• You are OK updating the query when new attribute types need to be added
• You would prefer a result that can be JOINed to, UNIONed, or nested as a subselect

Table A (Files)

FileID, Title, Author, CreatedOn

Table B (Attributes)

AttrID, AttrName, AttrType [not sure how you use type...]

Table C (Files_Attributes)

FileID, AttrID, AttrValue

A traditional query would pull many redundant rows:

SELECT * FROM 
Files F 
LEFT JOIN Files_Attributes FA USING (FileID)
LEFT JOIN Attributes A USING (AttributeID);

AttrID  FileID  Title           Author  CreatedOn   AttrValue   AttrName    AttrType
50      1       TestFile        Joe     2011-01-01  true        ReadOnly        bool
60      1       TestFile        Joe     2011-01-01  xls         FileFormat      text
70      1       TestFile        Joe     2011-01-01  false       Private         bool
80      1       TestFile        Joe     2011-01-01  2011-10-03  LastModified    date
60      2       LongNovel       Mary    2011-02-01  json        FileFormat      text
80      2       LongNovel       Mary    2011-02-01  2011-10-04  LastModified    date
70      2       LongNovel       Mary    2011-02-01  true        Private         bool
50      2       LongNovel       Mary    2011-02-01  true        ReadOnly        bool
50      3       ShortStory      Susan   2011-03-01  false       ReadOnly        bool
60      3       ShortStory      Susan   2011-03-01  ascii       FileFormat      text
70      3       ShortStory      Susan   2011-03-01  false       Private         bool
80      3       ShortStory      Susan   2011-03-01  2011-10-01  LastModified    date
50      4       ProfitLoss      Bill    2011-04-01  false       ReadOnly        bool
70      4       ProfitLoss      Bill    2011-04-01  true        Private         bool
80      4       ProfitLoss      Bill    2011-04-01  2011-10-02  LastModified    date
60      4       ProfitLoss      Bill    2011-04-01  text        FileFormat      text
50      5       MonthlyBudget   George  2011-05-01  false       ReadOnly        bool
60      5       MonthlyBudget   George  2011-05-01  binary      FileFormat      text
70      5       MonthlyBudget   George  2011-05-01  false       Private         bool
80      5       MonthlyBudget   George  2011-05-01  2011-10-20  LastModified    date

This coalescing query (approach using MAX) can merge the rows:

SELECT
F.*,
MAX( IF(A.AttrName = 'ReadOnly', FA.AttrValue, NULL) ) as 'ReadOnly',
MAX( IF(A.AttrName = 'FileFormat', FA.AttrValue, NULL) ) as 'FileFormat',
MAX( IF(A.AttrName = 'Private', FA.AttrValue, NULL) ) as 'Private',
MAX( IF(A.AttrName = 'LastModified', FA.AttrValue, NULL) ) as 'LastModified'
FROM 
Files F 
LEFT JOIN Files_Attributes FA USING (FileID)
LEFT JOIN Attributes A USING (AttributeID)
GROUP BY
F.FileID;

FileID  Title           Author  CreatedOn   ReadOnly    FileFormat  Private LastModified
1       TestFile        Joe     2011-01-01  true        xls         false   2011-10-03
2       LongNovel       Mary    2011-02-01  true        json        true    2011-10-04
3       ShortStory      Susan   2011-03-01  false       ascii       false   2011-10-01
4       ProfitLoss      Bill    2011-04-01  false       text        true    2011-10-02
5       MonthlyBudget   George  2011-05-01  false       binary      false   2011-10-20

The general form of such a query would be

SELECT file.*,
   attr1.value AS 'Attribute 1 Name', 
   attr2.value AS 'Attribute 2 Name', 
   ...
FROM
   file 
   LEFT JOIN attr AS attr1 
      ON(file.FileId=attr1.FileId and attr1.AttributeId=1)
   LEFT JOIN attr AS attr2 
      ON(file.FileId=attr2.FileId and attr2.AttributeId=2)
   ...

So you need to dynamically build your query from the attributes you need. In php-ish pseudocode

$cols="file";
$joins="";

$rows=$db->GetAll("select * from Attributes");
foreach($rows as $idx=>$row)
{
   $alias="attr{$idx}";
   $cols.=", {$alias}.value as '".mysql_escape_string($row['AttributeName'])."'";   
   $joins.="LEFT JOIN attr as {$alias} on ".
       "(file.FileId={$alias}.FileId and ".
       "{$alias}.AttributeId={$row['AttributeId']}) ";
}

 $pivotsql="select $cols from file $joins";