That is an unusual way to get results from a database. Normally you would do something like this
SELECT p.id, p.drawing_name, n.nr, n.Material
from pieces p
inner join normativo n on p.drawing_name = n.drawing
order by p.id
which would give the results
id drawing_name nr Material
1 D1234 1 TUBE
1 D1234 2 SHEET METAL
2 C1234 1 BAR
If you want to display that in a different way then that is a job for the display layer of your application.
There is a way to kludge it together e.g.
;with cte1 as
(
SELECT p.id, p.drawing_name, n.nr, n.Material
from pieces p
inner join normativo n on p.drawing_name = n.drawing
), cte2 as
(
select id, nr, Material, drawing_name
from cte1
union
select p.id, null as nr, null as Material, p.drawing_name
from pieces p
)
select
isnull(nr,id)
,isnull(Material, drawing_name)
from cte2
order by id, case when material is null then 0 else 1 end
Which gives
(No column name) (No column name)
1 D1234
1 TUBE
2 SHEET METAL
2 C1234
1 BAR
Similar to your expected results but not the same - largely because I don't think your expected results match the data you have given us, and we have nothing really to determine the order Edit: Corrected my error in the ORDER BY clause