How to normalize column values in new column?-CodePudding

I've below data in table, val1,val2,val3 are of double type. x represents record is having old value and y represents new value. So we need to concatenate all double values in respective col (oldVal,newVal) based on x and y value

ID,region,status,val1,val2,val3
1, aa,    x,    10,  11,  13
1, aa,    y,    12,  14,  15
2, bb,    x,    null,  null,  null
2, bb,    y,    null,  null,  null

expected output, oldVal,newVal are of varchar type

ID,region,oldVal,newVal
1, aa,10-11-13, 12-14-15
1, bb,null, null

how to de-normalize can be done for this to get expected output ?

Note: If any of the value is null out of (val1, val2, val3), then null value should not be considered.

1,2,null > 1-2

CodePudding user response：

You can try to use the condition aggregate function with CONCAT_WS function.

SELECT ID,
       region,
       MAX(CASE WHEN status = 'x' THEN CONCAT_WS('-',val1,val2,val3) END) oldVal,
       MAX(CASE WHEN status = 'y' THEN CONCAT_WS('-',val1,val2,val3) END) newVal
FROM T
GROUP BY ID,region

sqlfiddle