CODEWITHSUNDEEP
sqlsql-server
Houssem Timoumi
Houssem Timoumi

Reputation: 53

How can I split the result of a set of data based on the content of two columns?

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

Answers (2)

Gordon Linoff
Gordon Linoff

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

Popeye
Popeye

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

Related Questions