Un-pivot aka transpose from columns to rows using SQL or UNPIVOT Teradata function.

Data preparation:

CREATE  VOLATILE TABLE product
( 
  id INTEGER,
  name VARCHAR(10),
  depth INTEGER,
  height INTEGER,
  width INTEGER
)
ON COMMIT PRESERVE ROWS;

INSERT INTO product(1,'fridge',55,120,60);
INSERT INTO product(2,'heater',30,35,25);

SELECT * FROM product;
/*
id	name	depth	height	width
2	heater	30	35	25
1	fridge	55	120	60
*/

CREATE VOLATILE TABLE dimensions ( dimension VARCHAR(10))  ON COMMIT PRESERVE ROWS;

INSERT INTO dimensions ('depth');
INSERT INTO dimensions ('height');
INSERT INTO dimensions ('width');

SELECT * FROM dimensions;
/*
dimension
depth
height
width
*/

Solution using SQL

SELECT
  a1.id,
  a1.name,
  a2.dimension,
  CASE a2.dimension
     WHEN 'depth' THEN a1.depth
     WHEN 'height' THEN a1.height
     WHEN 'width' THEN a1.width
  END AS dimension_value
FROM product a1 CROSS JOIN dimensions a2
;
/*
id	name	dimension	dimension_value
1	fridge	depth	55
1	fridge	height	120
1	fridge	width	60
2	heater	depth	30
2	heater	height	35
2	heater	width	25
*/

Solution using Teradata UNPIVOT function

SELECT * FROM TD_UNPIVOT
(
 ON (SELECT * FROM product)
 USING
 VALUE_COLUMNS('dimension_value')
 UNPIVOT_COLUMN('dimension')
 COLUMN_LIST('depth', 'height', 'width')
 COLUMN_ALIAS_LIST('depth', 'height', 'width')
)X
ORDER BY 1,3
;
/*
id	name	dimension	dimension_value
1	fridge	depth	55
1	fridge	height	120
1	fridge	width	60
2	heater	depth	30
2	heater	height	35
2	heater	width	25
*/