MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

WITH articles AS (
    SELECT 1 AS article_id, 'First Car' AS title UNION ALL
    SELECT 2, 'Second Car'
),
articles_attr AS (
    SELECT 1 AS article_id, 1 AS attr_id UNION ALL
    SELECT 1, 2 UNION ALL
    SELECT 1, 3 UNION ALL
    SELECT 1, 5 UNION ALL
    SELECT 2, 3 UNION ALL
    SELECT 2, 4
),
attr_groups AS (
    SELECT 1 AS attr_id, 1 AS attr_group_id, 'red' AS attribute UNION ALL
    SELECT 2, 2, 'diesel' UNION ALL
    SELECT 3, 3, 'automatic' UNION ALL
    SELECT 4, 3, 'airbag' UNION ALL
    SELECT 5, 3, 'radio'
),
attr_groups_names AS (
    SELECT 1 AS attr_group_id, 'color' AS name UNION ALL
    SELECT 2, 'engine' UNION ALL
    SELECT 3, 'features'
)

SELECT
    a.article_id,
    a.title,
    GROUP_CONCAT(CASE WHEN agn.name = 'color'  THEN ag.attribute END) color,
    GROUP_CONCAT(CASE WHEN agn.name = 'engine' THEN ag.attribute END) engine,
    GROUP_CONCAT(CASE WHEN agn.name = 'features' THEN ag.attribute END) features
FROM articles a
INNER JOIN articles_attr aa
    ON a.article_id = aa.article_id
INNER JOIN attr_groups ag
    ON ag.attr_id = aa.attr_id
INNER JOIN attr_groups_names agn
    ON agn.attr_group_id = ag.attr_group_id
GROUP BY
    a.article_id,
    a.title;

article_id	title	color	engine	features
1	First Car	red	diesel	automatic,radio
2	Second Car	null	null	automatic,airbag