Spacko Spacko - 7 months ago 30
SQL Question

SQL Server Merge 2 rows into 1

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

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