SQL query case when without else or do nothing?

Question

Supposed I have some sample data in table_name_a as below:

    code     val_a  val_b    remark   date
   ------------------------------------------
1   00001    500    0.1      111      20191108
2   00001    1000   0.2      222      20191109
3   00002    200    0.1      111      20191110
4   00002    400    0.3      222      20191111
5   00001    200    0.2      333      20191112
6   00001    400    0.1      444      20191113

My expected output as below:

        code    111_a  111_b 222_a 222_b 333_a ....
       --------------------------------------------
    1   00001    500    ..    ..     ..   ..    

And below SQL query will contain 0 value which covered the correct values, does it possible to query without Else or do nothing?

SELECT code, date,
       CASE WHEN t.remark='111' THEN t.val_a ELSE 0 END 111_a,
       CASE WHEN t.remark='111' THEN t.val_b ELSE 0 END 111_b,
       CASE WHEN t.remark='222' THEN t.val_a ELSE 0 END 222_a,
       CASE WHEN t.remark='222' THEN t.val_b ELSE 0 END 222_b,
       CASE WHEN t.remark='333' THEN t.val_a ELSE 0 END 333_a,
       CASE WHEN t.remark='333' THEN t.val_b ELSE 0 END 333_b,
       CASE WHEN t.remark='444' THEN t.val_a ELSE 0 END 444_a,
       CASE WHEN t.remark='444' THEN t.val_b ELSE 0 END 444_b,
FROM table_name_a t

Answer 1

You need to use MAX with pivoting logic here, though you are on the right track:

SELECT
    code,
    MAX(CASE WHEN remark = '111' THEN val_a END) 111_a,
    MAX(CASE WHEN remark = '111' THEN val_b END) 111_b,
    MAX(CASE WHEN remark = '222' THEN val_a END) 222_a,
    MAX(CASE WHEN remark = '222' THEN val_b END) 222_b,
    MAX(CASE WHEN remark = '333' THEN val_a END) 333_a,
    MAX(CASE WHEN remark = '333' THEN val_b END) 333_b,
    MAX(CASE WHEN remark = '444' THEN val_a END) 444_a,
    MAX(CASE WHEN remark = '444' THEN val_b END) 444_b
FROM table_name_a
GROUP BY
    code;

The logic here works because for each group of records belonging to a single code, we take the maximum value only under certain conditions, in this case for certain values of remark. Should the condition be false, then the CASE expressions would return NULL, which would then be ignored by MAX.