Question: I have a record in my table:

[![enter image description here](https://i.stack.imgur.com/3U3TU.png)](https://i.stack.imgur.com/3U3TU.png)

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:

[![enter image description here](https://i.stack.imgur.com/nEDfu.png)](https://i.stack.imgur.com/nEDfu.png)

How can I do this?

Answer: 
One way to do this is to join your table multiple times with a constraint on the join to limit your result set; this is an easy way to pivot your data, but it can affect performance.

```
DECLARE @a TABLE (id INT, createdate date,canceldate date,reportdate DATE)

INSERT INTO @a (id, createdate, canceldate, reportdate)
VALUES (
    1, -- id - int
    GETDATE(), -- createdate - date
    GETDATE(), -- canceldate - date
    GETDATE() -- reportdate - date
)

INSERT INTO @a (id, createdate, canceldate, reportdate)
VALUES (
    2, -- id - int
    GETDATE(), -- createdate - date
    null, -- canceldate - date
    GETDATE() -- reportdate - date
)

SELECT a.id,a.createdate,a.canceldate,a.reportdate,CASE WHEN a1.id IS NOT NULL THEN '1' ELSE 0 END AS 'createdInd'
,CASE WHEN a2.id IS NOT NULL THEN '1' ELSE 0 END AS 'CancelledInd'

FROM @a a 
LEFT  JOIN @a a1 ON a.id = a1.id AND a1.createdate IS NOT NULL
LEFT JOIN @a a2 ON a.id = a2.id AND a2.canceldate IS NOT NULL 

id  createdate  canceldate  reportdate  createdInd  CancelledInd
1   2021-04-07  2021-04-07  2021-04-07  1   1
2   2021-04-07  NULL    2021-04-07  1   0

```
Join to the table a query that returns the values `1` and `0`:

```
SELECT t.*, s.OrderState
FROM tablename AS t
INNER JOIN (SELECT 1 AS OrderState UNION ALL SELECT 0) AS s
ON (s.OrderState = 1 AND t.OrderCreateDate IS NOT NULL)
OR (s.OrderState = 0 AND t.OrderCancelDate IS NOT NULL)

```