Merge two rows in SQL

Question

Assuming I have a table containing the following information:

FK | Field1 | Field2
=====================
3  | ABC    | *NULL*
3  | *NULL* | DEF

is there a way I can perform a select on the table to get the following

FK | Field1 | Field2
=====================
3  | ABC    | DEF

Thanks

Edit: Fix field2 name for clarity

Answer 1

There are a few ways depending on some data rules that you have not included, but here is one way using what you gave.

SELECT
    t1.Field1,
    t2.Field2
FROM Table1 t1
    LEFT JOIN Table1 t2 ON t1.FK = t2.FK AND t2.Field1 IS NULL

Another way:

SELECT
    t1.Field1,
    (SELECT Field2 FROM Table2 t2 WHERE t2.FK = t1.FK AND Field1 IS NULL) AS Field2
FROM Table1 t1

Answer 2

There might be neater methods, but the following could be one approach:

SELECT    t.fk,
          (
             SELECT t1.Field1 
             FROM   `table` t1 
             WHERE  t1.fk = t.fk AND t1.Field1 IS NOT NULL
             LIMIT  1
          ) Field1,
          (
             SELECT t2.Field2
             FROM   `table` t2 
             WHERE  t2.fk = t.fk AND t2.Field2 IS NOT NULL
             LIMIT  1
          ) Field2
FROM      `table` t
WHERE     t.fk = 3
GROUP BY  t.fk;

Test Case:

CREATE TABLE `table` (fk int, Field1 varchar(10), Field2 varchar(10));

INSERT INTO `table` VALUES (3, 'ABC', NULL);
INSERT INTO `table` VALUES (3, NULL, 'DEF');
INSERT INTO `table` VALUES (4, 'GHI', NULL);
INSERT INTO `table` VALUES (4, NULL, 'JKL');
INSERT INTO `table` VALUES (5, NULL, 'MNO');

Result:

+------+--------+--------+
| fk   | Field1 | Field2 |
+------+--------+--------+
|    3 | ABC    | DEF    |
+------+--------+--------+
1 row in set (0.01 sec)

Running the same query without the WHERE t.fk = 3 clause, it would return the following result-set:

+------+--------+--------+
| fk   | Field1 | Field2 |
+------+--------+--------+
|    3 | ABC    | DEF    |
|    4 | GHI    | JKL    |
|    5 | NULL   | MNO    |
+------+--------+--------+
3 rows in set (0.01 sec)

Answer 3

Aggregate functions may help you out here. Aggregate functions ignore NULLs (at least that's true on SQL Server, Oracle, and Jet/Access), so you could use a query like this (tested on SQL Server Express 2008 R2):

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

I used MAX, but any aggregate which picks one value from among the GROUP BY rows should work.

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');

Results:

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