CTE Common Type Expression

For providing pivoting in tables say you want to view your data in such a form such that rows comes in form of columns and columns in form of rows. For doing that SQL provide facility to use CTE in SQL

Ex : Suppose you have query in simple form

SELECT * FROM  (
        SELECT A.Address, E.Name, E.Age From Address A
        Inner join Employee E on E.EID = A.EID) T
WHERE T.Age > 50
ORDER BY T.NAME

By converting it into CTE it will look like this

With T(Address, Name, Age)  --Column names for Temporary table
AS
(
SELECT A.Address, E.Name, E.Age from Address A
INNER JOIN EMP E ON E.EID = A.EID
)
SELECT * FROM T  --SELECT or USE CTE temporary Table
WHERE T.Age > 50
ORDER BY T.NAME