MySQL 5.6

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

create table a(id int not null primary key auto_increment, p_id int not null, col varchar(10) not null);
insert into a(p_id, col) 
values
     (1, 'Kolumna 1'), (2, 'Kolumna 2'), (3, 'Kolumna 3'),
     (4, 'Kolumna 4'), (5, 'Kolumna 5'), (6, 'Kolumna 6'),
     (7, 'Kolumna 7'), (8, 'Kolumna 8'), (9, 'Kolumna 9');

create table b(id int not null primary key auto_increment, p_id int not null, name varchar(10), value varchar(10));
insert into b(p_id, name, value) 
values
     (1, 'type', '1'),(1, 'max_value', '50'),
     (2, 'type', '1'),(2, 'max_value', '100'),
     (3, 'type', '2'),(3, 'min_value', '40'),(3, 'max_value', '80'),
     (4, 'type', '2'),(4, 'min_value', '30'),(4, 'max_value', '60'),
     (5, 'type', '2'),(5, 'min_value', '20'),(5, 'max_value', '60'),
     (6, 'type', '3'),(6, 'min_value', '15'),
     (7, 'type', '3'),(7, 'min_value', '70'),
     (8, 'type', '3'),(8, 'min_value', '10');
select * from a join b on a.p_id=b.p_id;


id
p_id
col
id
p_id
name
value


1
1
Kolumna 1
1
1
type
1

1
1
Kolumna 1
2
1
max_value
50

2
2
Kolumna 2
3
2
type
1

2
2
Kolumna 2
4
2
max_value
100

3
3
Kolumna 3
5
3
type
2

3
3
Kolumna 3
6
3
min_value
40

3
3
Kolumna 3
7
3
max_value
80

4
4
Kolumna 4
8
4
type
2

4
4
Kolumna 4
9
4
min_value
30

4
4
Kolumna 4
10
4
max_value
60

5
5
Kolumna 5
11
5
type
2

5
5
Kolumna 5
12
5
min_value
20

5
5
Kolumna 5
13
5
max_value
60

6
6
Kolumna 6
14
6
type
3

6
6
Kolumna 6
15
6
min_value
15

7
7
Kolumna 7
16
7
type
3

7
7
Kolumna 7
17
7
min_value
70

8
8
Kolumna 8
18
8
type
3

8
8
Kolumna 8
19
8
min_value
10


select a.id, a.col name,
   max(case when b.name='type' then b.value end) type,
   max(case when b.name='min_value' then b.value end) min_value,
   max(case when b.name='max_value' then b.value end) max_value
from
   a
   join
   b
   on a.p_id=b.p_id
group by
   a.id, a.col;


id
name
type
min_value
max_value


1
Kolumna 1
1
null
50

2
Kolumna 2
1
null
100

3
Kolumna 3
2
40
80

4
Kolumna 4
2
30
60

5
Kolumna 5
2
20
60

6
Kolumna 6
3
15
null

7
Kolumna 7
3
70
null

8
Kolumna 8
3
10
null


select a.id, a.col name,
   max(case when b.name='type' then b.value end) type,
   max(case when b.name='min_value' then b.value end) min_value,
   max(case when b.name='max_value' then b.value end) max_value
from
   a
   join
   b
   on a.p_id=b.p_id
group by
   a.id, a.col
order by
   type,
   case when max(case when b.name='type' then b.value end)=1 
             then max(case when b.name='max_value' then b.value end)
        when max(case when b.name='type' then b.value end)=2
             then max(case when b.name='max_value' then b.value end)
        when max(case when b.name='type' then b.value end)=3
             then max(case when b.name='min_value' then b.value end)
   end;


id
name
type
min_value
max_value


2
Kolumna 2
1
null
100

1
Kolumna 1
1
null
50

5
Kolumna 5
2
20
60

4
Kolumna 4
2
30
60

3
Kolumna 3
2
40
80

8
Kolumna 8
3
10
null

6
Kolumna 6
3
15
null

7
Kolumna 7
3
70
null


select a.id, a.col name,
   max(case when b.name='type' then b.value end) type,
   max(case when b.name='min_value' then b.value end) min_value,
   max(case when b.name='max_value' then b.value end) max_value
from
   a
   join
   b
   on a.p_id=b.p_id
group by
   a.id, a.col
order by
   type,
   case when max(case when b.name='type' then b.value end)=1 
             then max(case when b.name='max_value' then b.value end)
        when max(case when b.name='type' then b.value end)=2
             then (
                   max(case when b.name='max_value' then b.value end),
                   max(case when b.name='min_value' then b.value end)
                  )
        when max(case when b.name='type' then b.value end)=3
             then max(case when b.name='min_value' then b.value end)
   end;
Operand should contain 1 column(s)

select a.id, a.col name,
   max(case when b.name='type' then b.value end) type,
   max(case when b.name='min_value' then b.value end) min_value,
   max(case when b.name='max_value' then b.value end) max_value
from
   a
   join
   b
   on a.p_id=b.p_id
group by
   a.id, a.col
order by
   type,
   case when max(case when b.name='type' then b.value end)=1 
             then max(case when b.name='max_value' then b.value end)
        when max(case when b.name='type' then b.value end)=2
             then (max_value, min_value)
        when max(case when b.name='type' then b.value end)=3
             then max(case when b.name='min_value' then b.value end)
   end;
Reference 'max_value' not supported (reference to group function)

id	p_id	col	id	p_id	name	value
1	1	Kolumna 1	1	1	type	1
1	1	Kolumna 1	2	1	max_value	50
2	2	Kolumna 2	3	2	type	1
2	2	Kolumna 2	4	2	max_value	100
3	3	Kolumna 3	5	3	type	2
3	3	Kolumna 3	6	3	min_value	40
3	3	Kolumna 3	7	3	max_value	80
4	4	Kolumna 4	8	4	type	2
4	4	Kolumna 4	9	4	min_value	30
4	4	Kolumna 4	10	4	max_value	60
5	5	Kolumna 5	11	5	type	2
5	5	Kolumna 5	12	5	min_value	20
5	5	Kolumna 5	13	5	max_value	60
6	6	Kolumna 6	14	6	type	3
6	6	Kolumna 6	15	6	min_value	15
7	7	Kolumna 7	16	7	type	3
7	7	Kolumna 7	17	7	min_value	70
8	8	Kolumna 8	18	8	type	3
8	8	Kolumna 8	19	8	min_value	10

id	name	type	min_value	max_value
1	Kolumna 1	1	null	50
2	Kolumna 2	1	null	100
3	Kolumna 3	2	40	80
4	Kolumna 4	2	30	60
5	Kolumna 5	2	20	60
6	Kolumna 6	3	15	null
7	Kolumna 7	3	70	null
8	Kolumna 8	3	10	null