Get table columns with no data or null values in mysql

Question

I have some table from which I fetch data for analytics. I need some workarounds to find records which have empty data or null values in the columns. The hard part is that I need to eliminate record columns which contain data. Please see the below example to get a good understanding.

+----+------+------+------+------+
| ID | col1 | col2 | col3 | col4 |
+----+------+------+------+------+
| 1  | Val1 | Val2 | Val3 | NULL |
| 2  | NULL | Val2 | NULL | Val4 |
| 3  | Val1 | Val2 | Val3 |      |
+----+------+------+------+------+

Is it possible to get an output like below using a query?

+------+------+------+
| 1    |  2   |   3  |
+------+------+------+
| col4 | col1 | col4 |
|      | col3 |      |
|      |      |      |
+------+------+------+

It could certainly be done if you used another language to pull the resultset into (like Python, Perl, Ruby, .NET, etc.). I'm having a hard time seeing how to do it in pure SQL w/o making a big mess though. Perhaps a pivot? — sniperd
– sniperd, Commented Jul 12, 2018 at 13:37
Unfortunately, I cannot use any other languages like PHP or Python. I am trying for a workaround using MySQL if there is any @sniperd — 웃웃웃웃웃
– 웃웃웃웃웃, Commented Jul 12, 2018 at 13:40
It's 5.6 @BillKarwin. Can you please provide with a reference link or something to check more about? Thanks — 웃웃웃웃웃
– 웃웃웃웃웃, Commented Jul 12, 2018 at 13:44

Bill Karwin · Accepted Answer · 2018-07-12 14:02:43Z

Tested on MySQL 5.6.37:

select 
 max(if(d.rownum=r.rownum and d.id=1,d.val,null)) as `1`,
 max(if(d.rownum=r.rownum and d.id=2,d.val,null)) as `2`,
 max(if(d.rownum=r.rownum and d.id=3,d.val,null)) as `3`
from (select 1 as rownum union select 2 union select 3 union select 4) as r
left join (
 select t.id, t.val, @n:=if(t.id=@id,@n+1,1) as rownum, @id:=t.id
 from (select @n:=0 as n, @id:=0 as id) as _init
 cross join (
  select id, 'col1' as val from mytable where col1 is null
  union select id, 'col2' from mytable where col2 is null
  union select id, 'col3' from mytable where col3 is null
  union select id, 'col4' from mytable where col4 is null
  order by id, val) as t) as d
   on r.rownum = d.rownum
group by r.rownum;

Output:

+------+------+------+
| 1    | 2    | 3    |
+------+------+------+
| col4 | col1 | col4 |
| NULL | col3 | NULL |
| NULL | NULL | NULL |
| NULL | NULL | NULL |
+------+------+------+

Collectives™ on Stack Overflow

Get table columns with no data or null values in mysql

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