Merge two rows in SQL

by

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

More Related Contents:

  1. What is the difference between JOIN and UNION?
  2. How to display data in JOIN, and return null value? [closed]
  3. INNER JOIN ON vs WHERE clause
  4. What is the difference between UNION and UNION ALL?
  5. SQL update from one Table to another based on a ID match
  6. SQL JOIN: is there a difference between USING, ON or WHERE?
  7. Difference between JOIN and INNER JOIN
  8. Why isn’t SQL ANSI-92 standard better adopted over ANSI-89?
  9. Difference between Oracle’s plus (+) notation and ansi JOIN notation?
  10. How to get matching data from another SQL table for two different columns: Inner Join and/or Union?
  11. SQL to LINQ with multiple join, count and left join
  12. SQL left join vs multiple tables on FROM line?
  13. Oracle “(+)” Operator
  14. JOIN two SELECT statement results
  15. SQL Joins Vs SQL Subqueries (Performance)?
  16. FULL OUTER JOIN with SQLite
  17. Is there something wrong with joins that don’t use the JOIN keyword in SQL or MySQL?
  18. Can we use join for two different database tables?
  19. Mixing implicit and explicit JOINs
  20. MySQL – Selecting data from multiple tables all with same structure but different data
  21. Record returned from function has columns concatenated
  22. Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail
  23. Difference between RIGHT & LEFT JOIN vs RIGHT & LEFT OUTER JOIN in SQL [duplicate]
  24. PostgreSQL JOIN with array type with array elements order, how to implement?
  25. How to exclude rows that don’t join with another table?
  26. SQL Inner Join On Null Values
  27. INNER JOIN vs multiple table names in “FROM” [duplicate]
  28. Is NATURAL (JOIN) considered harmful in production environment?
  29. Re-Writing “Fuzzy Join” Functions from R to SQL
  30. Procedurally transform subquery into join

Leave a Comment