Skip to content
Advertisement

SQL new table with conditions

I have a table and I want through sql to parse it into another table. The problem is that there is a condition: There are 3 columns in old version

column_1 column_2 column_3
    0            1      1
    1            0      1
    1            1      1

And I want to store them into new table’s column like :

new_column_1

no/yes/yes
yes/no/yes
yes/yes/yes

Thanks in advance.

Advertisement

Answer

You can use case expressions and string concatenation, like so:

select t.*,
    (case when column_1 = 1 then 'yes' else 'no' end)
    || '/' || (case when column_2 = 1 then 'yes' else 'no' end)
    || '/' || (case when column_3 = 1 then 'yes' else 'no' end) as new_column_1
from mytable t

This uses standard string concatenation operator ||; some databases have another operator or function.

Some databases also support concat_ws(), which simplifies the expression a little:

select t.*,
    concat_ws('/', 
        (case when column_1 = 1 then 'yes' else 'no' end)
        (case when column_2 = 1 then 'yes' else 'no' end)
        (case when column_3 = 1 then 'yes' else 'no' end)
    ) as new_column_1
from mytable t

You can easily create a new table starting from this query, using the insert ... select syntax, although I would not recommend storing this derived information: instead, you can create a view, or add computed column in the original table.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement