Common Table Expression (CTE) in SQL Server 2008 R2 and get Column value split on comma as row

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: