Merge records into one

Question

I'm looking the best way to "flatten" the first three record's values into fourth record (as shown). The values will be text, and each record will only ever have one value in one of the value columns. Also, one account number may have one or even multiple records like the example. Thanks.

Note: I have seen a few other topics related to my question, but they aren't quite the same as mine:

Sql Merging multiple records into one record

Merge multiple records into one row in a table

AcctNum|Val1|Val2|Val3|
1      |x   |    |    |
1      |    |y   |    |
1      |    |    |z   |
1      |x   |y   |z   |

I must say I can't see the difference in what you are asking from this [Sql Merging multiple records into one record](http://stackoverflow.com/questions/16668685/sql-merging-multiple-records-into-one-record) . The answer seems good too. — Lennart Regebro, Jul 05 '13 at 14:30
Are those blank values NULLs or are they empty strings, and is it ever possible for a single AcctNum to have more than one record with a value in the Val1 field? — MatBailie, Jul 05 '13 at 14:30
@LennartRegebro - Err, no, that's not a duplicate of this... — MatBailie, Jul 05 '13 at 14:31
The empty places will be null values, not empty strings. AcctNum will never have more than one record with a value in the Val1 field. The same goes for Val2 and Val 3. — user17596, Jul 05 '13 at 14:35
Ah, I forgot to mention that this will be dealt with in a Python/SQL environment. — user17596, Jul 05 '13 at 14:37
@LennartRegebro - That question/answer is concatenating multiple values in to one. This is ignoring NULLs and finding the one available value, and doind so multiple times on multiple fields. — MatBailie, Jul 05 '13 at 14:39
If you have a preference to which items are kept http://stackoverflow.com/questions/8433241/sql-combining-incomplete might be worth looking at. — George, Jul 05 '13 at 14:55
Multiple times on multiple fields make no difference. Are the NULL values kept, I thought they weren't? In any case the `max()` trick from George's link works, which means it's a duplicate of *that*question. ;-) — Lennart Regebro, Jul 05 '13 at 15:10

score 1 · Accepted Answer · answered Jul 05 '13 at 14:38

SELECT
  AcctNum,
  MAX(Val1)   AS Val1,
  MAX(Val2)   AS Val2,
  MAX(Val3)   AS Val3
FROM
  yourTable
GROUP BY
  AcctNum

NULL is never the MAX or the MIN of any set of values. MAX() and MIN() will only return NULL if there are no rows, or all of the values are NULL.

This means that MAX() or MIN() will eliminate all of the NULL and return just the one available value.

spencer7593 · Answer 2 · 2013-07-05T14:54:08.293

The SQL way to do this is with aggregate functions and a GROUP BY, e.g.

SELECT t.AcctNum
     , MAX(t.Val1) AS Val1
     , MAX(t.Val2) AS Val2
     , MAX(t.Val3) AS Val3
  FROM mytab t
 WHERE t.AcctNum = 1
 GROUP BY t.AcctNum

If you want to return the three rows, plus this fourth row, you can use a UNION ALL operator:

SELECT t.AcctNum
     , t.Val1
     , t.Val2
     , t.Val3
  FROM mytab t
 WHERE t.AcctNum = 1
 UNION ALL
SELECT t.AcctNum
     , MAX(t.Val1) AS Val1
     , MAX(t.Val2) AS Val2
     , MAX(t.Val3) AS Val3
  FROM mytab t
 WHERE t.AcctNum = 1
 GROUP BY t.AcctNum

If you are returning more than one AcctNum, and you want the rows returned in a particular sequence, that can be tricky.

score 0 · Answer 3 · answered Jul 05 '13 at 14:38

0

The simplest way is aggregation:

select AcctNum, max(val1) as val1, max(val2) as val2, max(val3) as val3
from Yourtable t
group by AcctNum;

I assume the semicolon on the second line is a typo, and you don't really intend to remove it if present.

answered Jul 05 '13 at 14:38

Gordon Linoff

1,242,037
58
646
786

Merge records into one

3 Answers3