Introduced in SQL Server 2005, the common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE VIEW statement, as part of the view’s SELECT query. In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement.
SQL Server supports two types of CTEs—recursive and non-recursive. In this article, I explain how to create both types. The examples I provide are based on a local instance of SQL Server 2008.
State Table
CREATE TABLE [dbo].[tb_State](
[SID] [nchar](40) NOT NULL,
[Statename] [nvarchar](200) NULL
) ON [PRIMARY]
City Table
CREATE TABLE [dbo].[tb_City](
[CID] [nchar](40) NOT NULL,
[Statename] [nvarchar](200) NULL,
[Cityname] [nvarchar](500) NULL
) ON [PRIMARY]
Working with Common Table Expressions
You define CTEs by adding a WITH clause directly before your SELECT, INSERT, UPDATE, DELETE, or MERGE statement. The WITH clause can include one or more CTEs, as shown in the following syntax:
[WITH <common_table_expression> [,...]]
<common_table_expression>::=
cte_name [(column_name [,...])]
AS (cte_query)
Example of Common Table Expressions:
WITH cte_BRIJESH AS
(
SELECT a.CID,a.Statename,b.SID,SUBSTRING(',' + a.Cityname + ',', Number + 1,
CHARINDEX(',', ',' + a.Cityname + ',', Number + 1) - Number -1)AS CityName
FROM master..spt_values
cross join dbo.tb_City a
JOIN tb_State b ON a.Statename = b.Statename
where type = 'P'
and Number <= LEN(',' + a.Cityname + ',') - 1
AND SUBSTRING(',' + a.Cityname + ',', Number, 1) = ','
)
SELECT * FROM cte_BRIJESH
Result:
