How Can I Merge Rows with NULL Values in SQL Based on a Common Key?

How to Merge Rows in SQL

Problem:

Consider a table with the following data:

FK	Field1	Field2
3	ABC	NULL
3	NULL	DEF

The objective is to retrieve a merged row from the table, where Field1 and Field2 values are combined for the same FK:

FK	Field1	Field2
3	ABC	DEF

Solution:

To achieve this result, aggregate functions can be employed. Aggregate functions disregard NULL values, allowing for the merging of non-NULL values. For instance, the following query (tested in SQL Server Express 2008 R2) can be used:

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

The MAX function is used to select one value from the rows grouped by FK. Any aggregate function that retrieves a single value within the grouped rows can be employed.

Sample Data and Results:

Consider the following sample data:

CREATE TABLE table1 (FK int, Field1 varchar(10), Field2 varchar(10));

INSERT INTO table1 VALUES (3, 'ABC', NULL);
INSERT INTO table1 VALUES (3, NULL, 'DEF');
INSERT INTO table1 VALUES (4, 'GHI', NULL);
INSERT INTO table1 VALUES (4, 'JKL', 'MNO');
INSERT INTO table1 VALUES (4, NULL, 'PQR');

Executing the query on this sample data yields the following output:

FK  Field1  Field2
--  ------  ------
3   ABC     DEF
4   JKL     PQR

Thus, the rows are effectively merged, combining non-NULL values for the same FK in the Field1 and Field2 columns.

The above is the detailed content of How Can I Merge Rows with NULL Values in SQL Based on a Common Key?. For more information, please follow other related articles on the PHP Chinese website!