DISTINCT

DISTINCT specifies that repeated rows in the result set are collapsed into a single row.

SELECT DISTINCT Name AS ProductName, LEFT(ProductNumber, 2) AS ProductCode, ISNULL(color, 'No Color') AS Color [...]