Common Table Expression – CTE

Common Table Expression (CTE) is one of my favorite sql server construct, it helps us to write complex logic without creating temporary table to store intermediate result set. As  common table expression can include references to itself, it helps us to write recursive logic in a single query. For more details on CTE, you may refer documentation provided by Microsoft. Here you will see some interesting use cases of CTE.

  • Split words in a string to column values in a table

 

DECLARE @message VARCHAR(200) = ‘Welcome to 15 to 20 minutes dot com ‘; –Input string

;WITH myCTE([start], [end])
AS
(
SELECT 1 AS [start], CHARINDEX(' ', @message, 1) AS [end]

UNION all

SELECT [end] + 1 as [start], CHARINDEX(' ', @message, [end] + 1) AS [end] FROM myCTE WHERE [end] < LEN(@message)

)
SELECT SUBSTRING(@message, [start], [end] - [start]) AS [Values] FROM myCTE;

Below is the result of above query.

CTE_Result1

  • Calculate Factorial
DECLARE @number INT = 5;
;WITH myCTE (fact, num)
AS
(
SELECT @number * (@number -1) AS fact, @number -1 AS num
UNION ALL
SELECT fact * (num -1), num -1 FROM myCTE WHERE num > 1
) SELECT TOP 1 fact FROM myCTE ORDER BY fact DESC;

Below is the result of above query.

CTE_Result2

  • Write Fibonacci Numbers
DECLARE @limit INT = 20;
;WITH myCTE(a, b, cnt)
AS
(
SELECT 0 AS a, 1 AS b, 1
UNION ALL
SELECT b, a + b, cnt + 1 FROM myCTE WHERE cnt < @limit
) SELECT a FROM myCTE

Below is the result of above query.

CTE_Result3

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s