Crosstable multiple columns

[BI_Dev]

Hi - I have table as below.

ID	Stage1Start	Stage2Start	Stage1End	Stage2End	Stage1	Stage2
ABC	10/5/2021	10/30/2021	10/15/2021	9/15/2021	y	n
BCD	1/9/2021	10/9/2021	10/30/2021	10/5/2021	n	y

I want to just keep 3 columns as below.

ID	Stage	Stage_Y_N	Start	End
ABC	Stage1	y	10/5/2021	10/15/2021
ABC	Stage2	n	10/30/2021	9/15/2021
BCD	Stage1	n	1/9/2021	10/30/2021
BCD	Stage2	y	10/9/2021	10/5/2021

Is this doable ? Thank you much.

[Saravanan_Desingh · Accepted solution]

Try this,

tab1:
LOAD * INLINE [
    ID, Stage1Start, Stage2Start, Stage1End, Stage2End, Stage1, Stage2
    ABC, 10/5/2021, 10/30/2021, 10/15/2021, 9/15/2021, y, n
    BCD, 1/9/2021, 10/9/2021, 10/30/2021, 10/5/2021, n, y
];

tab2:
CrossTable(Stage, Stage_Y_N)
LOAD ID, Stage1, Stage2
Resident tab1;

tab3:
CrossTable(Stage, Start)
LOAD ID, Stage1Start As Stage1, Stage2Start As Stage2
Resident tab1;

tab4:
CrossTable(Stage, End)
LOAD ID, Stage1End As Stage1, Stage2End As Stage2
Resident tab1;

Left Join(tab2)
LOAD * Resident tab3;

Left Join(tab2)
LOAD * Resident tab4;

Drop Table tab1, tab3, tab4;