# Better ways of concating the value of columns

## All we need is an easy explanation of the problem, so here it is.

I have a table with below structure and sample data:

``````create table CUSTOMER_TEST
(
customer_num Number,
rel_one      Number,
rel_two      Number,
rel_three    Number,
rel_four     Number
)

Customer_num    Rel_one     Rel_two        Rel_three        Rel_four
--------------- ----------- -------------  --------------  --------------
1               7           12             1000               5
2               2            1               0                12
3              12           99              13                0
``````

The desired result is this :

``````   Customer_num    Rel_one     Rel_two        Rel_three        Rel_four      Relation_Code
--------------- ----------- -------------  --------------  --------------  ***************
1               7           12             1000               5           L07R12C99S05
2               2            1               0                12          L02R01C00S12
3              12           99              13                0           L12R99C13S00
``````

What I’ve written is this :

``````select   customer_num,
rel_one ,
rel_two,
rel_three,
rel_four,
'L' ||
case
when   rel_one > 99 then '99'
'R' ||
case
when   rel_two > 99 then '99'
'C' ||
case
when   rel_three > 99 then '99'
'S' ||
case
when   rel_four > 99 then '99'
else lpad(  rel_four,2,0) end as relation_code

from customer_test ;
``````

Does anybody have a better idea ? rather than using this much case statement ..

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

### Method 1

``````select   customer_num,
rel_one ,
rel_two,
rel_three,
rel_four,
'L' || lpad(LEAST(rel_one,   99), 2, 0) ||
'R' || lpad(LEAST(rel_two,   99), 2, 0) ||
'C' || lpad(LEAST(rel_three, 99), 2, 0) ||
'S' || lpad(LEAST(rel_four,  99), 2, 0)  as relation_code
from customer_test ;
``````

I hope that none value can be NULL or negative.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂