mysql column name and field value as row

Question

I am trying to feed a data visualization program which uses sql queries. In order to create a pie chart, it wants the input to look as follows:

+--+-----+-----+-----+
|SliceName|SliceValue
+--+-----+-----+-----+
|Slice 1  |1
+--+-----+-----+-----+
|Slice 2  |5
+--+-----+-----+-----+

Our product data is stored in a table like this:

+--+-----+-----+-----+
|ProductID|Slice 1 | Slice 2
+--+-----+-----+-----+
|123      |1       |5
+--+-----+-----+-----+

Additional products being stored on additional rows. We need to retrieve one of these rows using the ProductID, then create sql output which looks like the table in the first example. I have read some posts about "pivoting", but I'm not sure that is exactly what we need to do here.

Can anyone offer some pointers?

@NielsKeurentjes Is this what I need? This approach doesn't output the names of the columns from the initial table as rows. That is the thing I'm struggling the most with. I don't need to do any new counting, or summing. All the values are already present. I just need to basically turn a row into a table. — melchoir55, Sep 04 '14 at 22:55
Yep, that's what pivoting is about, and what is explained in the answers to that other question. SQL is about rows, the field names are just decoration. Pivoting is not a native capability of SQL therefore - metadata like the field name cannot suddenly become a 'value'. — Niels Keurentjes, Sep 04 '14 at 22:56

adey_888 · Answer 1 · 2014-09-05T02:41:50.320

Is it possible to change your database schema (and do you have the appetite to do so)? Your problem could be resolved by looking at data normalisation and the relationships within the database.

I would take the information relating to the slices out of the Products table and into a new table called Slices.

Have a look at this SQL Fiddle example.

You'll see that I've created a FK in the Slices table:

FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
    ON DELETE CASCADE
    ON UPDATE CASCADE

So that if you delete a row from the Products table, the data in the Slices table that relates to that ProductID will also be deleted. Also if you were to update the ProductID in the Products table, then this change would be cascaded to the Slices table too.

Now to get the data in the format that you want, you could either SELECT from Slices direct using ProductID or use a SQL statement with an INNER JOIN to SELECT based on another column in the Products table, e.g.

SELECT SliceName, SliceValue
FROM Slices s
INNER JOIN Products p ON s.ProductID = p.ProductID
WHERE ProductName = "widget"

Storing your slices in a separate table like this be more efficient and provide flexibility; unless you always have a fixed number of slices for every product.

mysql column name and field value as row

1 Answers1