SQL Server Merge 2 rows into 1

Question

Is it possible to merge 2 rows into a single row in SSRS 2008? Each part will have a record for each site

+---------------+-------+-------+
|Part Number    |Cost   |Site   |
+---------------+-------+-------+
|1              |2.4    |Site 1 |
|1              |68.8   |Site 2 |
+---------------+-------+-------+

Desired Result

+-----------+-------+-------+
|Part Number|Site 1 |Site 2 | 
+-----------+-------+-------+
| 1         |2.4    |68.8   |
+-----------+-------+-------+

Thank you

Answer 1

If you know your site numbers/names will not change dynamically then can use CASE WHEN:s

SELECT PartNumber,
MAX(CASE WHEN Site=1 THEN Cost ELSE NULL END) AS Site1_Cost,
MAX(CASE WHEN Site=2 THEN Cost ELSE NULL END) AS Site2_Cost
FROM Parts
GROUP BY PartNumber

By grouping we eliminated a NULL values...

Here link with SQL Fiddle example