Menu
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
376
We could use multiple queries to combine rows in SQL Server to form a String. In the below example, we will combine rows using the COALESCE Function. DECLARE @Names VARCHAR (MAX) SELECT @Names = COALESCE (@Names + ', ', '') + [FirstName] FROM [geek_demo] SELECT @Names AS [List of All Names]
The simplest way to merge rows is with an aggregate function such as MIN/MAX. These functions will ignore nulls (see MSDN) and can operate similarly to ISNULL/COALESCE with aggregation. For example: This will return the sample desired results.
When UPDATE is specified in the <merge_matched> clause, and more than one row of <table_source> matches a row in target_table based on <merge_search_condition>, SQL Server returns an error. The MERGE statement can't update the same row more than once, or update and delete the same row.
A. Using MERGE to do INSERT and UPDATE operations on a table in a single statement. A common scenario is updating one or more columns in a table if a matching row exists. Or, inserting the data as a new row if a matching row doesn't exist.
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
165
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
