SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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
Code

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
Code

And the output of the above sql multiple CTE query is as follows

t-sql-multiple-cte-example

I hope especially MS SQL Server developers will find this multiple SQL CTE example useful.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.