Introducing Common Table Expressions in SQL Server 2005 - Creating common table expressions based on views
(Page 4 of 5 )
Up until now we have used only bare SELECT statements. We didn't use any views (or any other database objects). Let us now create another CTE which is based on an existing view. Even though this example demonstrates only views, you are free to use other database objects also (such as user-defined functions, aggregates, and so on).
Before creating the common table expression, let us look at the view "vSalesPerson" which is already created in the AdventureWorks database.
create view Sales.vSalesPerson
as
SELECT s.SalesPersonID, c.Title, c.FirstName, c.MiddleName, c.LastName, c.Suffix, e.Title AS JobTitle, c.Phone, c.EmailAddress, c.EmailPromotion,
a.AddressLine1, a.AddressLine2, a.City, sp.Name AS StateProvinceName, a.PostalCode, cr.Name AS CountryRegionName, st.Name AS TerritoryName,
st.[Group] AS TerritoryGroup, s.SalesQuota, s.SalesYTD, s.SalesLastYear
FROM Sales.SalesPerson AS s INNER JOIN
HumanResources.Employee AS e ON e.EmployeeID = s.SalesPersonID LEFT OUTER JOIN
Sales.SalesTerritory AS st ON st.TerritoryID = s.TerritoryID INNER JOIN
Person.Contact AS c ON c.ContactID = e.ContactID INNER JOIN
HumanResources.EmployeeAddress AS ea ON e.EmployeeID = ea.EmployeeID INNER JOIN
Person.Address AS a ON ea.AddressID = a.AddressID INNER JOIN
Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID INNER JOIN
Person.CountryRegion AS cr ON cr.CountryRegionCode = sp.CountryRegionCode
The above view simply provides you with the entire details for every sales person. Let us start creating a simple non-recursive common table expression using the above view.
With SalesPersonDetails(ID,[Name],State,Country,Territory)
as
(
select SalesPersonID,(FirstName+MiddleName+LastName) as [Name],StateProvinceName,CountryRegionName,TerritoryName
from Sales.vSalesPerson
)
Select * from SalesPersonDetails
Order by ID
The above CTE retrieves the SalesPersonID, Name (which is concatenated with FirstName+MiddleName+LastName fields), state, country and territory of every sales person.
Next: Creating views from common table expressions >>
More MS SQL Server Articles
More By Jagadish Chaterjee
