Reputation: 53
I have the following returned table:
value_date end_date local_limit_amount
12/10/2020 04/27/2021 800000.000000
12/10/2020 07/22/2021 1000000.000000
12/18/2020 12/31/2020 1425000.000000
01/18/2021 12/31/2021 500000.000000
I would like to slice down these pieces of data as following All of the dates should be displayed independently twice as one date, the value date line should make the limit amount positive and the end date line should make the same amount negative
general_date local_limit_amount
12/10/2020 800000.000000
12/10/2020 1000000.000000
12/18/2020 1425000.000000
01/18/2021 500000.000000
04/27/2021 -800000.000000
07/22/2021 -1000000.000000
12/31/2020 -1425000.000000
12/31/2021 -500000.000000
Thank you all!
Upvotes: 0
Views: 420
Reputation: 1271151
You can unpivot using apply
:
select v.*
from t cross apply
(values (value_date, local_limit_amount),
(end_date, -local_limit_amount)
) v(general_date, local_limit_amount)
order by general_date;
I recommend apply
over union all
because it only scans the table once. That is not a big deal for small tables. But it can become much more important on larger tables or if the "table" is really a complicated query.
Upvotes: 1
Reputation: 35930
You can simply use union
or union all
according to your requirement as follows:
Select value_date, local_limit_amount
From your_table
Union all
Select end_date, -local_limit_amount
From your_table
Upvotes: 0