Create a view using Common Table Expression

You can use CTE to create a view. Although CTE is in memory, the View will be persisted and can be found in the VIEWS folder in Object Explorer.

I will use the same CTE that I used in the previous post to create the view.
Run this script in the query pane of SQL Server Management Studio (herein SQL Server Developer 2012).

USE Northwind
Go
---define common table expression
Create view CityFolk
as
WITH NEmp_CTE (FirstName,LastName,CITY)  /*Name: Nemp_CTE Column List:FirstName, LastName,City*/
AS
---define the CTE query
(SELECT FirstName,LastName,City
from Employees
where City in ('Redmond','London')
)
Select * from NEmp_CTE
go

You will get the 'Command(s) completed successfully response.

Now you can find the view CityFolk in the VIEWS folder as shown.

CTEView