The alternative could be the official unpivot.

SELECT Val

FROM (SELECT CAST( '1 cod ' + CAST( cod AS varchar(10)) AS varchar(20)) col1,

CAST( '1 des1' + des1 AS varchar(20)) col2,

CAST( '1 des2' + des2 AS varchar(20)) col3,

CAST( '8 S' AS varchar(20)) col4

FROM #prova) p

UNPIVOT(Val FOR Cols IN (col1, col2, col3, col4))u

Or you could use UNION ALL for 2000 and previous versions.