/* WITH example from http://idealprogrammer.com 
 
 PURPOSE: WITH allows you to create a temporary result set that is called a common table expression
 (CTE). The CTE has column names that match up with the column names listed in the SELECT statement
 that follows the AS keyword and that is enclosed in parentheses. When a CTE contains a reference
 to itself, this is called a recursive CTE. For a more complete description of syntax and examples,
 please refer to: http://msdn.microsoft.com/en-us/library/ms175972(v=SQL.105).aspx
 
 
 SYNTAX: 
 
 [ WITH common_table_expression [ (column_name [ ,...n ] ) ]
    AS
    ( SELECT [ ALL | DISTINCT ] 
			[TOP ( expression ) [PERCENT] [ WITH TIES ] ] 
			 column_list  		
			[ FROM { table_source } [ ,...n ] ] 
			[ WHERE select_condition] 
			[ GROUP BY ] 
			[ HAVING  search_condition  ] 
			[ ORDER BY  order_expression [ ASC | DESC ] ] )
 
 
*/
 
 
CREATE TABLE people(
     ID          INT,
     firstname   VARCHAR (20),
     lastname    VARCHAR (20),
     statecode   VARCHAR (2),
     alive       BIT,
     height      INT )
 GO
 
 
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height) 
 VALUES (1,  'Paul', 'Revere', 'AL', 0, 72)
 GO
 
 INSERT INTO people (ID, firstname, lastname, alive, height) -- statecode NULL
 VALUES (2,  'John', 'Lennon',  0, 69)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height)
 VALUES (3,  'Steve', 'Martin', 'NY', 1, 75)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height)
 VALUES (4,  'George', 'Washington', 'VA', 0, 75)
 GO
 
 
 
 --Display entire table
 SELECT 'Entire Table' AS Example,* FROM people
 
 
 
 
 -- 1. Example of CTE with simple SELECT 
 GO
 WITH people_CTE(CTE_Example, CTE_firstname, CTE_lastname, CTE_height)
 AS 
 (SELECT 'Example #1' AS Example, firstname, lastname, height 
  FROM people p
  WHERE height > 72)
 
  SELECT * 
  FROM people_CTE
 
 
 
 GO
 
 
 -- 2. For more examples, please see: 
 -- http://msdn.microsoft.com/en-us/library/ms175972(v=SQL.105).aspx
 
 
 
 DROP TABLE people;
 
 GO

/* WITH example from http://idealprogrammer.com PURPOSE: WITH allows you to create a temporary result set that is called a common table expression (CTE). The CTE has column names that match up with the column names listed in the SELECT statement that follows the AS keyword and that is enclosed in parentheses. When a CTE contains a reference to itself, this is called a recursive CTE. For a more complete description of syntax and examples, please refer to: http://msdn.microsoft.com/en-us/library/ms175972(v=SQL.105).aspx SYNTAX: [ WITH common_table_expression [ (column_name [ ,...n ] ) ] AS ( SELECT [ ALL | DISTINCT ] [TOP ( expression ) [PERCENT] [ WITH TIES ] ] column_list [ FROM { table_source } [ ,...n ] ] [ WHERE select_condition] [ GROUP BY ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ] ) */ CREATE TABLE people( ID INT, firstname VARCHAR (20), lastname VARCHAR (20), statecode VARCHAR (2), alive BIT, height INT ) GO INSERT INTO people (ID, firstname, lastname, statecode, alive, height) VALUES (1, 'Paul', 'Revere', 'AL', 0, 72) GO INSERT INTO people (ID, firstname, lastname, alive, height) -- statecode NULL VALUES (2, 'John', 'Lennon', 0, 69) GO INSERT INTO people (ID, firstname, lastname, statecode, alive, height) VALUES (3, 'Steve', 'Martin', 'NY', 1, 75) GO INSERT INTO people (ID, firstname, lastname, statecode, alive, height) VALUES (4, 'George', 'Washington', 'VA', 0, 75) GO --Display entire table SELECT 'Entire Table' AS Example,* FROM people -- 1. Example of CTE with simple SELECT GO WITH people_CTE(CTE_Example, CTE_firstname, CTE_lastname, CTE_height) AS (SELECT 'Example #1' AS Example, firstname, lastname, height FROM people p WHERE height > 72) SELECT * FROM people_CTE GO -- 2. For more examples, please see: -- http://msdn.microsoft.com/en-us/library/ms175972(v=SQL.105).aspx DROP TABLE people; GO