How to Combine Rows with NULL Values in SQL Using Aggregate Functions?

How to Merge Rows in SQL

Question:

Imagine a scenario where you have a database table with columns FK, Field1, and Field2. The table consists of data as follows:

FK	Field1	Field2
3	ABC	NULL
3	NULL	DEF

You aim to perform a query that combines these two rows into one, producing the following output:

FK	Field1	Field2
3	ABC	DEF

Answer:

Aggregate functions, such as MAX, can be utilized to achieve this data merging. They inherently ignore NULL values on platforms like SQL Server, Oracle, and Jet/Access. Here's a sample query that employs the MAX function:

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

In this query, we group the rows by the FK column and use the MAX function to retrieve the maximum value for each of the other columns (Field1 and Field2). Since NULL values are disregarded, the non-NULL values (ABC and DEF) are returned in the merged row.

Here's an example with 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');

When the query is executed on this test data, it yields the following output:

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

The above is the detailed content of How to Combine Rows with NULL Values in SQL Using Aggregate Functions?. For more information, please follow other related articles on the PHP Chinese website!