SQL Multiple CTE Syntax and T-SQL CTE Example in SQL Server 2008
This t-sql tutorial will give t-sql examples to show hot to use sql multiple CTE queries in one SQL Select statement.
Building multiple CTE in SQL Server 2005 or SQL2008 is very easy. As using single CTE queries there is only one WITH and each CTE definition is seperated from each other using comma ","
After the definition section of the sql cte examples, there comes the main select statement or sql update statement, etc.
In this part CTE tables can be used just as any other sql database table. You can join sql CTE tables using Inner Join, Outer Join types, etc.
Multiple CTE Syntax
Here is the basic sql CTE syntax for enabling how sql developers can use sql multiple CTE 's in one single SQL Select statement.
WITH CTE1 AS (
SELECT TOP 2 Name FROM Sales.Store
),
CTE2 AS (
SELECT TOP 2 ProductNumber, Name FROM Production.Product
),
CTE3 AS (
SELECT TOP 2 Name FROM Person.ContactType
)
SELECT * FROM CTE1,CTE2,CTE3
-- Or use INNER JOIN, LEFT JOIN instead of Cartesian Joins
Although the above multiple CTE using T-SQL Select query does not produce a meaningful output, in your MS SQL Server 2005 or SQL Server 2008 environment you will probably find solutions for your data manipulation requirements using multiple CTE queries.
I have used CTE in SQL Server 2005 in many cases and I have been using t-sql CTE in SQL Server 2008, too. Especially the sql CTE recursive queries are great help for sql developers who deals with tree structure records.
SQL Multiple CTE Example
Here is a simple sql multiple CTE which is consists of two CTE queries in one tsql Select statement.
If you download and install MS SQL Server 2008 sample databases, you can run the below multiple CTE Select statement directly on AdventureWorks2008 sample database.
WITH CTE1 AS (
select
ProductID, SUM(OrderQty) as TotalOrderQty
from Sales.SalesOrderDetail
group by ProductID
),
CTE2 AS (
select
p.ProductID, pc.ProductCategoryID, pc.Name
from Production.Product p
inner join Production.ProductSubcategory psc
on psc.ProductSubcategoryID = p.ProductSubcategoryID
inner join Production.ProductCategory pc
on pc.ProductCategoryID = psc.ProductCategoryID
)
SELECT * FROM (
SELECT
CTE2.ProductCategoryID,
CTE2.Name,
CTE1.ProductID,
CTE1.TotalOrderQty,
rn = ROW_NUMBER() OVER (PARTITION BY CTE2.ProductCategoryID ORDER BY CTE1.TotalOrderQty DESC)
FROM CTE1
INNER JOIN CTE2 on CTE1.ProductID = CTE2.ProductID
) CTE
WHERE rn <= 3
And the output of the above sql multiple CTE query is as follows
I hope especially MS SQL Server developers will find this multiple SQL CTE example useful.