I have previously written many articles
on CTE. One question I get often is how to use multiple CTE in one query
or multiple CTE in SELECT statement. Let us see quickly two examples
for the same. I had done my best to take simplest examples in this
subject.
Option 1 :
/* Method 1 */
;WITH CTE1 AS (SELECT 1 AS Col1),
CTE2 AS (SELECT 2 AS Col2)
SELECT CTE1.Col1,CTE2.Col2
FROM CTE1
CROSS JOIN CTE2
GO
Option 2:
/* Method 2 */
;WITH CTE1 AS (SELECT 1 AS Col1),
CTE2 AS (SELECT COL1+1 AS Col2 FROM CTE1)
SELECT CTE1.Col1,CTE2.Col2
FROM CTE1
CROSS JOIN CTE2
GO
Please note the difference between
options. If there is any other option than above mentioned two options,
please leave your comment here.
Reference : Pinal Dave (http://blog.sqlauthority.com)
No comments:
Post a Comment