Question Details

No question body available.

Tags

sql sql-server sql-server-2022

Answers (4)

Accepted Answer Available
Accepted Answer
August 5, 2025 Score: 5 Rep: 6,826 Quality: High Completeness: 100%

I think following query with a COALESCE trick is enough:

SELECT 
  t1.ID,
  t1.Account,
  t2.Contact
FROM MyTable AS t1
JOIN MyTable AS t2
  ON t1.ID = t2.ID
  AND COALESCE(t1.Contact, '')  t2.Contact;

Above query self-joins on ID and filters out same-contact pairs.
COALESCE(t1.Contact, '') t2.Contact ensures:

  • NULLs can be compared as if they were values (by treating NULL as '')

  • So the join works for both:

    • one NULL + one non-NULL → match

    • two non-NULLs → swap

Without COALESCE, NULL value would be unknown and skipped.

The query returns the expected result:

ID Account Contact
ID1 A12 C11
ID2 A21 C22
ID3 A31 C32
ID3 A32 C31

Tested with your sample data on this dbfiddle

Note you might need to extend this in case your real data includes more than two non-NULL rows per ID or duplicate Account values per ID.

August 5, 2025 Score: 3 Rep: 79,958 Quality: Medium Completeness: 70%

You can do this in a single scan of the base table by using window functions.

We can rely on the fact that nulls sort first to check if we are the second row but the first one was not null.

SELECT
  mt.ID,
  mt.Account,
  CASE WHEN mt.rn = 1 THEN mt.NextContact ELSE mt.PrevContact END AS Contact
FROM (
    SELECT *,
      LAG(mt.Contact)  OVER (PARTITION BY mt.ID ORDER BY mt.Contact) AS PrevContact,
      LEAD(mt.Contact) OVER (PARTITION BY mt.ID ORDER BY mt.Contact) AS NextContact,
      ROW_NUMBER()     OVER (PARTITION BY mt.ID ORDER BY mt.Contact) AS rn
    FROM MyTable mt
) mt
WHERE rn = 1 OR PrevContact IS NOT NULL;

dbfiddle

August 6, 2025 Score: 1 Rep: 18,062 Quality: Low Completeness: 50%

If you don't want to deal with some of the messiness of null logic, I might argue for using the other two columns for the join. Those might even work better with indexes and it may even reflect the business semantics better.

SELECT
    t1.ID,
    t1.Account,
    t2.Contact
FROM MyTable t1 INNER JOIN MyTable t2
    ON t2.ID = t1.ID AND t2.Account  t1.Account
WHERE t2.Contact IS NOT NULL;

And it should be noted that several of the answers -- this one too -- are using the assumption that there are exactly two accounts per id.

August 5, 2025 Score: -5 Rep: 1 Quality: Low Completeness: 50%

Here's the updated code...

-- One NULL and one NOT NULL
SELECT
  nullrow.ID,
  nullrow.Account,
  notnullrow.Contact
FROM
  MYtable nullrow
  JOIN MYtable notnullrow
    ON nullrow.ID = notnullrow.ID
WHERE
  nullrow.Contact IS NULL
  AND notnullrow.Contact IS NOT NULL
UNION ALL
-- Two NOT NULL: swap contacts
SELECT
  t1.ID,
  t1.Account,
  t2.Contact
FROM
  MYtable t1
  JOIN MYtable t2
    ON t1.ID = t2.ID
    AND t1.Account  t2.Account
WHERE
  t1.Contact IS NOT NULL
  AND t2.Contact IS NOT NULL
ORDER BY
  ID,
  Account;