Tuesday, 3 September 2013

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


City Table

CREATE TABLE [dbo].[tb_City](
[CID] [nchar](40) NOT NULL,
[Statename] [nvarchar](200) NULL,
[Cityname] [nvarchar](500) NULL

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> [,...]]

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

Example of Common Table Expressions:

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) = ','