Daksh Sorathia Daksh Sorathia - 26 days ago 9
MySQL Question

Sql group join with all fields individually

Bellow is my mysql table structure and i want result like last table format.
please give me or hint me regarding this sql query or solution.

i try to some group or order query but not getting look that.

tbl_id sub_id num1 num2 date
2 6 100 0 2017-09-21
3 6 0 1000 2017-09-20

Actually I want

sub_id num1 num1-date num2 num2-date
6 100 2017-09-21 1000 2017-09-20

Answer Source


SELECT x.sub_id
     , MAX(x.num1) num1
     , MAX(CASE WHEN x.num1 <> 0 THEN x.date END) num1_date
     , MAX(x.num2) num2
     , MAX(CASE WHEN x.num2 <> 0 THEN x.date END) num2_date 
  FROM my_table x 
    BY x.sub_id;

But, generally, I think it's best to resolve issues of data display such as this in application code.