w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Sorting over two columns in entity-attribute-value schema

So, to recap, you have table something like this...

DROP TABLE IF EXISTS eav_hell;

CREATE TABLE eav_hell
(entity INT NOT NULL
,attribute VARCHAR(12) NOT NULL
,value VARCHAR(12)
);

INSERT INTO eav_hell VALUES
(1,'Brand','Tatra'),
(1,'Year','2005'),
(2,'Brand','Aston Martin'),
(2,'Year','1999'),
(3,'Brand','Man'),
(3,'Year','2005');

...from which you can obtain a result something like this...

SELECT entity
     , MAX(CASE WHEN attribute = 'Brand' THEN value END) Brand
     , MAX(CASE WHEN attribute = 'Year' THEN value END) Year
  FROM eav_hell
 GROUP
    BY entity;   

+--------+--------------+------+
| entity | Brand        | Year |
+--------+--------------+------+
|      1 | Tatra        | 2005 |
|      2 | Aston Martin | 1999 |
|      3 | Man          | 2005 |
+--------+--------------+------+

...so what was the problem again?

(and pray pity the poor data types)





© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.