A couple ways you can accomplish what you need.
The first would be with a group by with a MAX condition.
SELECT
p.name,
p.`year`,
MAX(IF(p.`month` = 1, p.value, NULL)) AS month1,
MAX(IF(p.`month` = 2, p.value, NULL)) AS month2,
MAX(IF(p.`month` = 3, p.value, NULL)) AS month3,
MAX(IF(p.`month` = 4, p.value, NULL)) AS month4
FROM table_name AS p
GROUP BY p.name, p.`year`
Another approach would be with joins for each month you want to retrieve.
SELECT
p.name,
p.`year`,
m1.value AS month1,
m2.value AS month2,
m3.value AS month3,
m4.value AS month4
FROM table_name AS p
LEFT JOIN table_name AS m1
ON m1.name = p.name AND m1.`year` = p.`year` AND m1.`month` = 1
LEFT JOIN table_name AS m2
ON m2.name = p.name AND m2.`year` = p.`year` AND m2.`month` = 2
LEFT JOIN table_name AS m3
ON m3.name = p.name AND m3.`year` = p.`year` AND m3.`month` = 3
LEFT JOIN table_name AS m4
ON m4.name = p.name AND m4.`year` = p.`year` AND m4.`month` = 4
#....
WHERE p.name = 'foo' AND p.`year` = 2017
GROUP BY p.name, p.`year`;
Demo: http://sqlfiddle.com/#!9/6de54a/3
Results of both would be the same, however your ID would no longer be relevant, as you're grouping the individual rows into a single row, so I will leave it to you on how you want to display the ID.
Alternatively you can use PHP to transpose the data, separating the display by using an array key of the name and year.
Demo: https://3v4l.org/KvCD5
//SELECT * FROM table_name WHERE name = 'foo' AND `year` = '2017';
$data = [];
foreach($rows as $row) {
$key = $row['name'] . '_' . $row['year'];
if(!isset($data[$key])){
$data[$key] = array(
'id' => $row['id'],
'name' => $row['name'],
'year' => $row['year']
);
}
$data[$key]['month' . $row['month']] = $row['value'];
}
Result:
Array
(
[foo_2017] => Array
(
[id] => 1
[name] => foo
[year] => 2017
[month1] => bar1
[month2] => bar2
[month3] => bar3
[month4] => bar4
)
)
Then you would iterate over the transposed data.
<table>
<tr>
<th>id</th>
<th>name</th>
<th>year</th>
<th>month1</th>
<th>month2</th>
<th>month3</th>
<th>month4</th>
</tr>
<?php foreach($data as $row) { ?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['name']; ?></td>
<td><?php echo $row['year']; ?></td>
<td><?php echo $row['month1']; ?></td>
<td><?php echo $row['month2']; ?></td>
<td><?php echo $row['month3']; ?></td>
<td><?php echo $row['month4']; ?></td>
</tr>
<?php } ?>
</table>
