UNPIVOT -
Use of Unpivot isto convert column into rows. which is basica reversal of Pivot function. It’s used to normalize the denormalized data structure.

Eg. -

CREATE TABLE SalesData (
 ProductID INT,
    Q1 INT,
    Q2 INT,
    Q3 INT,
    Q4 INT
);
INSERT INTO SalesData VALUES (1, 100, 200, 150, 300);

ProductID	Q1	Q2	Q3	Q4
1	100	200	150	300

Applying Unpivot

SELECT ProductID, Quarter, Sales
FROM SalesData 
UNPIVOT (Sales FOR Quarter IN (Q1, Q2, Q3, Q4)) AS Unpvt;

Output Becomes

ProductID	Quarter	Sales
1	Q1	100
1	Q2	200
1	Q3	150
1	Q4	300

CROSS APPLY -
Joins a table function with each row of the outer query, acting like a correlated subquery. It Used for splitting, filtering, or applying table-valued functions on row-by-row basis.

Eg. -

CREATE TABLE EmployeeProjects (
 EmployeeID INT,
 Projects VARCHAR(100)
);
INSERT INTO EmployeeProjects VALUES (1, 'ProjectA,ProjectB,ProjectC');

EmployeeID	Projects
1	ProjectA,ProjectB,ProjectC

Applying CROSSAPPLY

SELECT e.EmployeeID, p.Value AS ProjectName
FROM EmployeeProjects e
CROSS APPLY STRING_SPLIT(e.Projects, ',') p;

Output -

EmployeeID	ProjectName
1	ProjectA
1	ProjectB
1	ProjectC