October 7, 2005 at 12:12 pm
Hi Guys,
I had the same problem, but my table design is a little different
DROP TABLE metaTable;
DROP TABLE metaType;
DROP TABLE metaAttribute;
DROP TABLE metaValue;
CREATE TABLE metaTable(
ID INT,
Name VARCHAR(80)
);
INSERT INTO metaTable(ID, Name) VALUES(1, 'Product');
CREATE TABLE metaType(
ID INT,
Name VARCHAR(40)
);
INSERT INTO metaType(ID, Name) VALUES(1, 'STRING');
INSERT INTO metaType(ID, Name) VALUES(1, 'INT');
INSERT INTO metaType(ID, Name) VALUES(1, 'DOUBLE');
CREATE TABLE metaAttribute(
ID INT,
TableID INT,
Name VARCHAR(80),
TypeID INT
);
INSERT INTO metaAttribute(ID, TableID, Name, TypeID) VALUES(1, 1, 'ProductID', 1);
INSERT INTO metaAttribute(ID, TableID, Name, TypeID) VALUES(2, 1, 'Name1' , 1);
INSERT INTO metaAttribute(ID, TableID, Name, TypeID) VALUES(3, 1, 'Name2' , 1);
INSERT INTO metaAttribute(ID, TableID, Name, TypeID) VALUES(4, 1, 'Unit' , 2);
INSERT INTO metaAttribute(ID, TableID, Name, TypeID) VALUES(5, 1, 'Price' , 3);
CREATE TABLE metaValue(
TableID INT,
AttributeID INT,
RowID INT,
Value VARCHAR(255)
);
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 1, 1, '1210809');
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 2, 1, 'Knifes');
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 3, 1, 'black 8cm');
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 4, 1, '1');
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 5, 1, '3.49');
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 1, 2, '4711');
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 2, 2, 'Coffee Mugs');
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 3, 2, 'red, glass');
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 4, 2, '3');
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 5, 2, '1.99');
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 1, 3, '1233213');
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 2, 3, 'Phone');
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 3, 3, NULL);
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 4, 3, '1');
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 5, 3, '12.49');
When you created the tables run the followin statement
SELECT
RowID,
MAX(CASE WHEN mv.AttributeID = 1 THEN mv.Value END) AS ArtikelNr,
MAX(CASE WHEN mv.AttributeID = 2 THEN mv.Value END) AS Name1,
MAX(CASE WHEN mv.AttributeID = 3 THEN mv.Value END) AS Name2,
MAX(CASE WHEN mv.AttributeID = 4 THEN mv.Value END) AS PreisEH,
MAX(CASE WHEN mv.AttributeID = 5 THEN mv.Value END) AS Preis
FROM metaValue mV
WHERE TableID = 1
GROUP BY RowID
This design allows me to be flexible enough to create new columns for my "Product-Table" on the fly. Especially usefull if you have different clients which need different attributes for their products 🙂
My question now is:
I use the MAX()-Function to aggregate the result. Is this a performant way to do so? Or maybe there is a better solution
TIA
Michael
October 7, 2005 at 12:30 pm
Max() or min() it diesn't matter, this is the best performing solution to perform a crosstab query.
the only other way is to create temporary objects and a looping/cursor solution where you update rows in a temp table to the value, this is a much poorer performing solution.
I think other that the fact that your table is not normalized to 3nf in the traditional sense your doing it okay.
October 7, 2005 at 12:34 pm
What part of it is actually normalised??? That's gonna be the real performance killer.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply