ADD IN ONE TABLE ROWS FROM DIFFERENT TABLES

Question

NEED TO CREATE NORMATIVE FOR PRODUCTION
I HAVE PEACES: tbl.pieces
 1. D1234
 2. C1234

FOR  PIECE 1 NEED MATERIAL: tbl.normativo
 1. TUBE
 2. SHEET METAL

FOR PIECE 2 NEED MATERIAL:tbl.normativo
 2.BAR

Result in final table (DATAGRID) need to be

1. D1234
    1. TUBE
    2. SHEET METAL
2. C1234
    1. BAR

I TRY TO UNDERSTAND LOGIC OF MS SQL

SELECT FROM dbo.pieces

Result:
id  drawing_name
1.    D1234
2.    C1234

Select * from dbo.normativo

Result:
Nr  Material       drawing
1. TUBE            D1234
2. SHEET METAL     D1234
1. BAR             C1234