Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE eav (
    id SERIAL NOT NULL PRIMARY KEY,
    model_id INTEGER NOT NULL,
    model TEXT NOT NULL,
    attribute TEXT NOT NULL,
    value TEXT
);
INSERT INTO eav(model_id, model, attribute, value) 
VALUES 
    (1, 'product', 'id', '1'),
    (1, 'product', 'fucktory', 'Восход'),
    (1, 'product', 'material', 'Пластик'),
    (2, 'product', 'id', '2'),
    (2, 'product', 'fucktory', 'Заход'),
    (2, 'product', 'material', 'Чугуний');
6 rows affected

SELECT 
    id.value AS id,
    fucktory.value AS fucktory,
    material.value AS material
FROM eav AS id
LEFT JOIN eav AS fucktory
    ON fucktory.model_id = id.value::INT
    AND fucktory.model = 'product'
    AND fucktory.attribute = 'fucktory'
LEFT JOIN eav AS material
    ON material.model_id = id.value::INT
    AND material.model = 'product'
    AND material.attribute = 'material'
WHERE 
    id.model = 'product'
    AND id.attribute = 'id'


id
fucktory
material


1
Восход
Пластик

2
Заход
Чугуний