Merge two rows in SQL

Using Aggregate functions for non-null values

Handling NULL values? No problem. MAX function's got our back. It can blend non-null values perfectly, like a well-prepared smoothie:

SELECT
  FK,
  MAX(Field1) AS Field1,
  MAX(Field2) AS Field2
FROM
  your_table
GROUP BY
  FK;


Noticed how it got all the non-null values from Field1 and Field2 based on each FK into unique rows? Now, that's a smooth operation.

Subqueries: The Merging Sidekick

Subqueries are like butlers, they fetch what you need right where it is needed. Just ensure to return the right rows to avoid misplacing your precious data:

SELECT DISTINCT
  t1.FK,
  (SELECT MAX(t2.Field1) FROM your_table t2 WHERE t2.FK = t1.FK) AS Field1,
  (SELECT MAX(t3.Field2) FROM your_table t3 WHERE t3.FK = t1.FK) AS Field2
FROM
  your_table t1;


You can think of subqueries like SQL's room service: "Fetch me the MAX value from this room called your_table".

Preserving data integrity and dodging performance issues

Data integrity and performance are the dynamic duo of every SQL management story. Here are some tips to keep both in check:

• Use WHERE clauses to filter like a barista brewing the perfect blend.
• Don't invite subqueries that may return more than a single row per key.
• Ensure join values stay consistent across all subqueries like a dedicated ballet dancer.

Here's how LEFT JOIN steps in for that high-efficiency merge:

SELECT
  t1.FK,
  COALESCE(t1.Field1, t2.Field1) AS Field1,
  COALESCE(t1.Field2, t2.Field2) AS Field2
FROM
  your_table t1
LEFT JOIN
  your_table t2 ON t1.FK = t2.FK AND t1.Field1 IS NULL;


When complexity strikes, remember: Maintainability, Scalability, Simplicity.

The Art of Merging with Different Servers

In SQL Server land, the STRING_AGG function is the trusty sidekick of GROUP_CONCAT:

SELECT
  customer_id,
  SUM(amount) AS total_amount,
  STRING_AGG(product_name, ', ') WITHIN GROUP (ORDER BY product_name) AS products
FROM
  sales
GROUP BY
  customer_id;


It's like a tiny SQL DJ, mixing all your product names into one groovy string.

How to merge with conditions?

Use CASE statements inside your aggregation methods for selective merging. It's like choosing the fruit for your juice blend:

SELECT
  FK,
  SUM(CASE WHEN condition1 THEN amount ELSE 0 END) AS condition1_total,
  MAX(CASE WHEN condition2 THEN Field ELSE NULL END) AS condition2_field
FROM
  your_table
GROUP BY
  FK;


Now you're juic-*ahem*, merging like a pro!