Multiple CTE (Common Table Expression) in a Query

Posted: September 14, 2010 in SQL Server 2005

A CTE (Common Table Expression) is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. You can use multiple CTE’s in a query as follows:

-- Declare first CTE expression
WITH cte_first AS 
(
   SELECT 'Column1' As Column1,
          'Column2' AS Column2
),
-- Declare second CTE expression
cte_second AS 
(
   SELECT 'Column3' AS Column3
)

-- Select the results
SELECT cte1.*,
       cte2.*
FROM   cte_first            cte1
       CROSS JOIN cte_second cte2;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

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