How Can I Merge Multiple SQL Rows into a Single Row Using Aggregate Functions?

Merging Rows in SQL

When working with databases, you may encounter situations where you need to combine data from multiple rows into a single record. Consider the following table containing two fields, "Field1" and "Field2":

FK	Field1	Field2
3	ABC	NULL
3	NULL	DEF

The goal is to retrieve the following combined data:

FK	Field1	Field2
3	ABC	DEF

To merge these rows, aggregate functions can be utilized. Aggregate functions, such as MAX, ignore NULL values. By applying the MAX aggregate function to both "Field1" and "Field2," you can obtain the maximum non-NULL value for each group, effectively combining the data into a single row.

The following query demonstrates this approach:

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

This query groups rows by the "FK" column and applies the MAX aggregate function to "Field1" and "Field2." The result is a table where each row represents the combined data from the original split rows.

As an example, consider the following 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');

Running the merge query on this data produces the following results:

FK	Field1	Field2
3	ABC	DEF
4	JKL	PQR

By leveraging aggregate functions, you can effectively merge data from multiple rows into a single, combined record.

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