How Can SQL Aggregate Functions Merge Multiple Rows with NULL Values into a Single Row?

Selecting Data to Merge Multiple Rows in SQL

Consider a table with data as shown below:

FK	Field1	Field2
3	ABC	NULL
3	NULL	DEF

The goal is to perform a SELECT operation to combine these rows into a single row:

FK	Field1	Field2
3	ABC	DEF

Aggregate Function Solution

Aggregate functions can be employed to ignore NULL values and combine the non-NULL values from multiple matching rows. Here's a query that demonstrates this approach:

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

In this query, we use the MAX aggregate function to select the maximum value for Field1 and Field2 from the rows grouped by FK. Since NULL values are ignored by aggregate functions, this ensures that only non-NULL values are combined.

Testing the Solution

Using the provided test 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');

The query yields the desired results:

FK	Field1	Field2
3	ABC	DEF
4	JKL	PQR

The above is the detailed content of How Can SQL Aggregate Functions Merge Multiple Rows with NULL Values into a Single Row?. For more information, please follow other related articles on the PHP Chinese website!