Introducing Common Table Expressions in SQL Server 2005 - Can we group the output available from CTE?
(Page 3 of 5 )
You can do everything that a SELECT is meant to do. But please be aware that this is applicable only to the SELECT statement which is outside the WITH (and not the internal one). You can use almost all clauses in the outer SELECT statement.
As part of an example, let us rewrite the above sample to display the number of employees grouped by their managers. Consider the following script:
With EmployeeManagers(EmployeeName,Manager) as
(
SELECT a.Name , b.Name
FROM (SELECT HumanResources.Employee.EmployeeID,
isnull(Person.Contact.FirstName,'') + ' ' + isnull(Person.Contact.MiddleName,'') + ' ' + isnull(Person.Contact.LastName,'') AS Name,
HumanResources.Employee.ManagerID
FROM Person.Contact INNER JOIN
HumanResources.Employee ON Person.Contact.ContactID = HumanResources.Employee.ContactID) AS a LEFT OUTER JOIN
(SELECT HumanResources.Employee.EmployeeID,
isnull(Person.Contact.FirstName,'') + ' ' + isnull(Person.Contact.MiddleName,'') + ' ' + isnull(Person.Contact.LastName,'') AS Name,
HumanResources.Employee.ManagerID
FROM Person.Contact INNER JOIN
HumanResources.Employee ON Person.Contact.ContactID = HumanResources.Employee.ContactID) AS b ON a.ManagerID = b.EmployeeID
)
select Manager, count(*) from EmployeeManagers
group by Manager
order by Manager
You can observe that within the above script, instead of using the normal SELECT, I am using SELECT with GROUP BY and ORDER BY.
Next: Creating common table expressions based on views >>
More MS SQL Server Articles
More By Jagadish Chaterjee
