Remove NULL cell in SELECT mysql?

Question

I have a table YEAR like this

I want to select this table, so that I can get

I wonder if it is possible to get table where every cell is independent (not as one row/field).

Why do you store denormalized data like this? Why do you need to display it like this? And how do you define that A C is returned and not A D or A E? Simply do it in the client.... — dnoeth
– dnoeth, Commented Aug 14, 2016 at 17:43
It just sort ASC, it can be B E H G, A D NULL F and NULL C NULL NULL (desc). I just wonder if we can see table not as one row, but every row and column are independent. I want to remove all NULL value that is in the top, so the alphabet can go up. — khusnanadia
– khusnanadia, Commented Aug 14, 2016 at 17:57

Gordon Linoff · Accepted Answer · 2016-08-14 17:20:13Z

3

This is a real pain, but possible. The idea is to enumerate the values in each column and then "join" them together. The join is really a group by:

select max(`2014`) as `2014`,
       max(`2015`) as `2015`,
       . . .
from ((select (@rn2014 := @rn2014 + 1) as rn, `2014`,
              NULL as `2015`, NULL as `2016`, NULL as `2017`
       from year
       where `2014` is not null
      ) union all
      (select (@rn2015 := @rn2015 + 1) as rn, NULL, `2015`, NULL, NULL
       from year
       where `2015` is not null
      ) union all
      . . .
    ) y
group by rn;

answered Aug 14, 2016 at 17:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Piotr Rogowski · Accepted Answer · 2016-08-14 18:47:51Z

I write solution this problem in postgresql, i know that author use mysql but the idea is the same

crate table

create table _year 
(
    _y_2014 text,
    _y_2015 text,
    _y_2016 text,
    _y_2017 text
)

insert data into table

INSERT INTO _year(_y_2014, _y_2015, _y_2016, _y_2017) select 'A',NULL,NULL,NULL;
INSERT INTO _year(_y_2014, _y_2015, _y_2016, _y_2017) select NULL,'C',NULL,NULL;
INSERT INTO _year(_y_2014, _y_2015, _y_2016, _y_2017) select NULL,'D',NULL,NULL;
INSERT INTO _year(_y_2014, _y_2015, _y_2016, _y_2017) select NULL,'E',NULL,NULL;
INSERT INTO _year(_y_2014, _y_2015, _y_2016, _y_2017) select 'B',NULL,NULL,NULL;
INSERT INTO _year(_y_2014, _y_2015, _y_2016, _y_2017) select NULL,NULL,NULL,'F';
INSERT INTO _year(_y_2014, _y_2015, _y_2016, _y_2017) select NULL,NULL,NULL,'G';
INSERT INTO _year(_y_2014, _y_2015, _y_2016, _y_2017) select NULL,NULL,'H',NULL;

select query:

select 
 e1.val as "2014",
 e2.val as "2015",
 e3.val as "2016",
 e4.val as "2017"
 from (SELECT counter from generate_series(1, (select count(*) from _year)) as counter) cc 
 left join (SeLECT 
    row_number() OVER (ORDER BY _y_2014) AS i,
    _y_2014 as val,
    '2014'::text as _y
    FROM _year
    where _y_2014 is not null) e1 on e1.i = cc.counter
 left join (SeLECT 
    row_number() OVER (ORDER BY _y_2015) AS i,
    _y_2015 as val,
    '2015'::text as _y
    FROM _year
    where _y_2015 is not null) e2 on e2.i = cc.counter
 left join (SeLECT 
    row_number() OVER (ORDER BY _y_2016) AS i,
    _y_2016 as val,
    '2016'::text as _y
    FROM _year
    where _y_2016 is not null) e3 on e3.i = cc.counter
 left join (SeLECT 
    row_number() OVER (ORDER BY _y_2017) AS i,
    _y_2017 as val,
    '2017'::text as _y
    FROM _year
    where _y_2017 is not null) e4 on e4.i = cc.counter
where e1.val is not null or e2.val is not null or e3.val is not null or e4.val is not null

Collectives™ on Stack Overflow

Remove NULL cell in SELECT mysql?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related