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.