Double record when both conditions are met SQL

Question

I have a record in my table:

What I need is to create a column with order state: '1' if order was created, '0' if order was cancelled.

So for this example, when there was both creation and cancellation I need two states. The final table should be:

How can I do this?

Answer 1

I think you can simply do a UNION like this:

select OrderCreateDate, OrderCancelDate, ReportDate, 1 as OrderState
  from your_table
  where orderCreateDate is not null
union all
select OrderCreateDate, OrderCancelDate, ReportDate, 0 as OrderState
  from your_table
  where orderCancelDate is not null