Pivoting Concept.

Question

Hiii,

I have a Database design as:

Table File (FileID, Name, Details)

Table Attributes (AttID, AttName, AttType)

Table AttValues (FileID, AttID, AttValue)

Till the runtime, it is not known how many Attributes are in a File and of what names.

And I want to display after insertion at Frontend in a manner like like:

FileID, FileName, Details, (Rows of Attribute Table as Column here).

i.e., Can anybody provide me a piece of code in Java or in MySQL to achieve this Pivoting Result.

Highly thanks full for your precious time.

Or is there any other better way to store data, So that I can get the desired result easily.

Answer 1

This requires two queries. First select the File:

SELECT * FROM File WHERE (...)

Then, fetch the Attributes:

SELECT *
FROM AttValues
     JOIN Attributes ON (Attributes.AttId = AttValues.AttId)
WHERE FileId = $id

The latter query will provide you with one row per Attribute, which you can programmatically pivot for display on your frontend:

foreach(row in result) {
    table.AddColumn(Header = row['AttName'], Value = row['AttValue']);
}

Adapt to your local programming environment as needed.

---

Of course, this only works for a single File or Files with the same attributes. If you want to display multiple files with different Attributes you can instead prefetch all AttNames:

SELECT Attributes.AttId, Attributes.AttName
FROM Attributes 
     JOIN AttValues ON (Attributes.AttId = AttValues.AttId)
WHERE FileId IN ( $list_of_ids )

Then load the values like this:

SELECT *
    FROM AttValues
WHERE FileId IN ( $list_of_ids )

and use a local associative array to map from AttIds to column indexes.

As a final optimisation, you can combine the last two queries into an OUTER JOIN to avoid the third round trip. While this will probably increase the amount of data transferred, it also makes filling the table easier, if your class library supports named columns.

Answer 2

If you're willing to suffer a bit of crazy implementations and slightly awkward code, there's a fairly flexible crosstabs implementation here: http://www.futhark.ch/mysql/106.html

Answer 3

I answered a similar question recently: How to pivot a MySQL entity-attribute-value schema. The answer is MySQL-specific, but I guess that's OK as the question is tagged with mysql.