logic to create more rows in sql

I have a table1 that I wanted to transform into the expected table.

• Expected table logic for columns:
• cal: comes from cal of table1. ID comes from the ID of table1.
• code: this is populated with lp or fp depending upon if we have a value in f_a then we create a new record with fp as code. corresponding to it we check if f_a is populated if yes then we take that date from f_a and put in in the Al column for the same ID. also we check if f_pl is populated if yes then we take the date from that and put it in the pl column.
• If the code was lp then we check if l_a is populated then we take that date and place in the date in Al for that code and Id. also, we check if lpl is populated if yes then we take that date and put it in pl.

I am just a starter with SQL so it is a bit overwhelming for me on how to get it started. Please post some solutions.

table1:

ID  f_a            l_a           f_pl       lpl               cal
CNT 6/20/2018      6/28/2018                6/28/2018         1/31/2020

expected output:

ID  Cal             code          pl            Al 
CNT 1/31/2020       lp        6/28/2018   6/28/2018 
CNT 1/31/2020       fp                    6/20/2018 

Update: I have more IDs in the table, so it is not that CNT is the only Id. If I use unpivot then it should follow the same logic for all IDs.

Answer

Please try this script which is not version dependend:

-- Here we select columns foom source table. Please change the names if they are different
with r as (
  select
    ID,
    f_a,
    l_a,
    f_pl,
    lpl, -- Here not sure if example is wrong or no underscore in column deffinition
    cal
  from table_1 --  Please put real table name here
)
select * from (
select r.id, r.cal, 'lp' as code, r.l_pl as pl, l_a as ai
from r
where r.l_a is not null
union all
select r1.id, r1.cal, 'pl', r1.f_pl, r1.f_a 
from r r1
where r1.f_a is not null
)
order by id, cal, code;