SELECT DISTINCT Order#,
OrderDate,
PaymentType,
Price,
RTRIM(SUBSTRING(ISNULL((SELECT ',' + ProductName
FROM your_table t2
WHERE t1.Order# = t2.Order#
for xml path('')),' '),2,2000)) ProductName,
SUM([Weight])
OVER(PARTITION BY Order# ) [Weight],
ShipType,
ShipVia,
Company,
FirstName,
LastName,
[Address],
City,
[State],
Zip,
Country,
Phone,
Email,
Comments
FROM your_table t1
Is that correct? If not, then let me know your filter creiterions and i will provide you the Sql query..