Common Table Expressions

 Syntax:

[WITH <common_table_expression> [,...]]

<common_table_expression>::=

cte_name [(column_name [,...])]

AS (cte_query)

CTE (Common Table Expression):
The CTE is one of the essential features in the sql server 2005.It just store the result as temp result set. It can be access like normal table or view. This is only up to that scope.

The syntax of the CTE is the following.

WITH name (Alias name of the retrieve result set fields)
AS
(
//Write the sql query here
)
SELECT * FROM name
Here the select statement must be very next to the CTE. The name is mandatory and the argument is an optional. This can be used to give the alias to the retrieve field of the CTE.

CTE 1: Simple CTE
WITH ProductCTE
AS(  SELECT ProductID AS [ID],ProductName AS [Name],CategoryID AS [CID],UnitPrice AS [Price]
  FROM Products
)SELECT * FROM ProductCTE
Here all the product details like ID, name, category ID and Unit Price will be retrieved and stored as temporary result set in the ProductCTE.

This result set can be retrieved like table or view.

CTE2:Simple CTE with alias
WITH ProductCTE(ID,Name,Category,Price)AS(  SELECT ProductID,ProductName,CategoryID,UnitPrice
  FROM Products
)SELECT * FROM ProductCTE
Here there are four fieds retrieves from the Products and the alias name have given in the arqument to the CTE result set name.

It also accepts like the following as it is in the normal select query.
WITH ProductCTE
AS(  SELECT ProductID AS [ID],ProductName AS [Name],CategoryID AS [CID],UnitPrice AS [Price]
  FROM Products
)SELECT * FROM ProductCTE
CTE 3: CTE joins with normal table
The result set of the CTE can be joined with any table and also can enforce the relationship with the CTE and other tables.
WITH OrderCustomer
AS(  SELECT DISTINCT CustomerID FROM Orders
)SELECT C.CustomerID,C.CompanyName,C.ContactName,C.Address+', '+C.City AS [Address] FROM Customers C INNER JOIN OrderCustomer OC ON OC.CustomerID = C.CustomerID
Here the Ordered Customers will be placed in the CTE result set and it will be joined with the Customers details.
CTE 4: Multiple resultsets in the CTE
WITH MyCTE1
AS(  SELECT ProductID,SupplierID,CategoryID,UnitPrice,ProductName FROM Products
), 

MyCTE2
AS(  SELECT DISTINCT ProductID FROM "Order Details"
)SELECT C1.ProductID,C1.ProductName,C1.SupplierID,C1.CategoryID FROM MyCTE1 C1 INNER JOIN MyCTE2 C2 ON C1.ProductID = C2.ProductID
Here, there are two result sets that will be filtered based on the join condition.

CTE 5: Union statements in the CTE
WITH PartProdCateSale
AS(SELECT ProductID FROM Products WHERE CategoryID = (SELECT CategoryID FROM Categories WHERE CategoryName='Condiments')UNION ALL
SELECT ProductID FROM Products WHERE CategoryID = (SELECT CategoryID FROM Categories WHERE CategoryName='Seafood')
)SELECT OD.ProductID,SUM(OD.UnitPrice*OD.Quantity) AS [Total Sale] FROM "Order Details" OD INNER JOIN PartProdCateSale PPCS ON PPCS.ProductID = OD.ProductID
GROUP BY OD.ProductID
Normally when we combine the many result sets we create table and then insert into that table. But see here, we have combined with the union all and instead of table, here CTE has used.

CTE 6: CTE with identity column
WITH MyCustomCTE
   AS   (      SELECT CustomerID,row_number() OVER (ORDER BY CustomerID) AS iNo FROM
         Customers
   )SELECT * FROM MyCustomCTE

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

-----GENERAL METHOD
  SELECT a.Payment_ID,a.Purchase_Order_ID,Payment_Date,Actual_Amount,a.Payment_Amount,
 Bank_Name,[Cheque_No/DD_No/Card_No],
 Created_On,Created_By,Modified_On,Modified_By,Is_Active,Is_Deleted
  FROM
  (SELECT MAX(Payment_ID) Payment_ID,Purchase_Order_ID,
  --ROW_NUMBER() OVER(PARTITION BY Purchase_Order_ID ORDER BY Purchase_Order_ID) Purchase_Order_ID ,
  SUM(Payment_Amount) Payment_Amount
  FROM Uams.AMS_Payment
  GROUP BY purchase_Order_ID) a
  JOIN
  Uams.AMS_Payment P
  ON a.Payment_ID=p.Payment_ID
 
 
-----WITH COMMON TABLE EXPRESSION
  WITH Cte_Payment(Payment_ID,Purchase_Order_ID,Payment_Amount)
  AS
  (SELECT MAX(Payment_ID) Payment_ID,Purchase_Order_ID,
  --ROW_NUMBER() OVER(PARTITION BY Purchase_Order_ID ORDER BY Purchase_Order_ID) Purchase_Order_ID ,
  SUM(Payment_Amount) Payment_Amount
  FROM Uams.AMS_Payment
  GROUP BY purchase_Order_ID)
 
 SELECT Cte_Payment.Payment_ID,Cte_Payment.Purchase_Order_ID,Payment_Date,Actual_Amount,Cte_Payment.Payment_Amount,
 Bank_Name,[Cheque_No/DD_No/Card_No],
 Created_On,Created_By,Modified_On,Modified_By,Is_Active,Is_Deleted
  FROM Cte_Payment
   JOIN
  Uams.AMS_Payment P
  ON Cte_Payment.Payment_ID=p.Payment_ID