SQL: Combine multiple rows with empty fields into one row

Question

Given such table:

+-----------+-----------+-----------+-----------+
|    id     |     A     |     B     |     C     |
+-----------+-----------+-----------+-----------+
|    100    |     1     |    NULL   |    NULL   |
+-----------+-----------+-----------+-----------+
|    101    |    NULL   |     2     |    NULL   |
+-----------+-----------+-----------+-----------+
|    102    |    NULL   |    NULL   |     3     |
+-----------+-----------+-----------+-----------+
|    103    |     4     |    NULL   |    NULL   |
+-----------+-----------+-----------+-----------+
|    104    |    NULL   |    NULL   |     5     |
+-----------+-----------+-----------+-----------+

Is there a way to make a query which combines the multiple rows into one to avoid empty fields?

Example result:

+-----------+-----------+-----------+
|     A     |     B     |     C     |
+-----------+-----------+-----------+
|     1     |     2     |     3     |
+-----------+-----------+-----------+
|     4     |    NULL   |     5     |
+-----------+-----------+-----------+

To notice how:

Each row contains only one value for the fields (and so A or B or C)
There isn't a field which can help grouping all fields of an entry
An entry can have a different number of fields (an entry could have A, B and C as fields, while another could have only A and C or similar).

Not sure if anything can be done about this?

Any suggestion is really appreciated!

Gordon Linoff · Accepted Answer · 2020-12-15 17:33:54Z

1

You want to condense the data. One method is union all with a trick:

select max(a), max(b), max(c)
from ((select a as val, null as b, null as c, row_number() over (order by id) as seqnum
       from t
       where a is not null
      ) union all
      (select null, b, null, row_number() over (order by id) as seqnum
       from t
       where b is not null
      ) union all
      (select null, null, c, row_number() over (order by id) as seqnum
       from t
       where c is not null
      ) 
     ) abc
group by seqnum;

answered Dec 15, 2020 at 17:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL: Combine multiple rows with empty fields into one row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related