---

Search posts under SQL Server:

---

Create a CTE (Common Table Expression) in SQL Server

How to create a CTE (Common Table Expression) and use it?

To create CTE, use highlighted approach. Read below SQL statements carefully

WITH PersonalCTEName (TotalSalary, Name) AS ( SELECT SUM(NetSalary), LastName FROM viewPersonalAccounts WHERE LastName LIKE 'N%' GROUP BY LastName )
SELECT * FROM PersonalCTEName
       UNION (
             SELECT SUM(NetSalary), LastName FROM viewPersonalAccounts
             WHERE NetSalary > 50000
             GROUP BY LastName
             ) ORDER BY Name

The CTE definition starts with “WITH” keyword followed by the name of the CTE and then the column that this CTE will return. Under bracket, we write the SQL statements to return the data from this CTE (this SQL statements can be any valid SQL statements).

Here, the first highlighted part is the CTE defined whose data is being fetched from the following SELECT statement (SELECT * FROM PersonalCTEName) and we are using UNION clause to append the data from next query (get sum of records whose NetSalary is more than 50000 grouped by LastName) and then the combination of record from CTE and UNION block result is being sorted by Name.