One of my favorite additions to T-SQL language in SQL Server 2005 is Common Table Expressions, CTE for short. Primarily I use them instead of derived tables to improve maintenance and code readability.
See also this interesting article on recursive CTE implementation T-SQL: Using common table expressions (CTE) to generate sequences and this interesting discussion about commonly-known problem of splitting delimited values.
You may find this article by SQLUSA (Kalman Toth) very interesting.
I think you will find these blogs by Brad Schulz Viva la Famiglia! and This Article On Recurson Is Entitled “This Article On Recursion Is Entitled “This Article… interesting and useful and enjoyable reading.
See also Challenge 17 and its solutions at Challenge 17 – Winners
For instance, this code is much easier to read now after moving some logic into CTE
;WITH
-- -----------------------------------------------
-- Calcualtes sum of active taxes
-- for each of Tax Groups
-- -----------------------------------------------
Taxes as
(
SELECT tg.nTaxGroupID as nTaxGroupID,
tg.cName as TaxGroupName,
Sum(isNull(ti.nPercent,0)) as TaxPercent
FROM TaxGroups tg
LEFT JOIN TaxGroupsToTaxItems tgi on tg.nTaxGroupID = tgi.nTaxGroupID
LEFT JOIN TaxItems ti on tgi.nTaxItemID = ti.nTaxItemID
WHERE tg.lActive = 1
GROUP BY tg.nTaxGroupID,
tg.cName
),
-- -----------------------------------------------
-- calculates total cost per Job
-- -----------------------------------------------
JDS as
(
SELECT nJobHeaderID,
Sum(nAmount * nQuantity) as TotalAmount
FROM JobDetail
GROUP BY nJobHeaderID
),
-- -----------------------------------------------
-- concatenates all Customer's phone numbers
-- into one string (LF delimited)
-- -----------------------------------------------
P as
(
SELECT nCustomerID,
Stuff(PhoneInfo, 1, 1, '') AS PhoneInfo
FROM ( SELECT InnerData.nCustomerID,
( SELECT Char(10) + P.cPhoneNumber + ' - ' + rTrim(PT.cType)
FROM PhoneNumbers P
INNER JOIN PhoneType PT on P.nPhoneTypeID = PT.nPhoneTypeID
WHERE P.nCustomerID = InnerData.nCustomerID
FOR XML PATH('') -- this does string concatenation
) as PhoneInfo
FROM ( SELECT DISTINCT nCustomerID
FROM PhoneNumbers
) as InnerData
) as OuterData
)
SELECT J.nRouteHeaderID as nRouteHeaderID,
JH.nCustomerID as nCustomerID,
JH.nJobHeaderID as JobHeaderID,
J.ScheduleTime as ScheduleTime,
JH.cContact as Contact,
J.SplitAmount as SplitAmount,
vJ.cAddr1 as Address1,
vJ.cAddr2 as Address2,
vJ.cCity as City,
vJ.cState as State,
vJ.cZip as Zip,
vJ.JobDescription as JobDescription,
JH.nDiscountPercent/100 * JDS.TotalAmount
as DiscountAmount,
JH.nAmount as JobTotal,
T.TaxGroupName as TaxType,
T.TaxPercent as TaxPercent,
J.PermanentNote as PermanentNote,
P.PhoneInfo as PhoneInfo
FROM @Jobs J
INNER JOIN dbo.JobHeader JH on J.nJobHeaderID = JH.nJobHeaderID
LEFT JOIN v_JobAddress vJ on J.nJobHeaderID = vJ.nJobHeaderID
LEFT JOIN Taxes T on JH.nTaxGroupID = T.nTaxGroupID
LEFT JOIN JDS JDS on JH.nJobHeaderID = JDS.nJobHeaderID
LEFT JOIN P P on JH.[nCustomerID] = P.[nCustomerID]
ORDER BY J.IdField -- Used for order but not retrieved
One of the most interesting applications of CTE is in solving hierarchical queries by using recursive CTEs. In SQL Server 2000 you would need to use temporary table and looping to solve the problem of hierarchical query, which can now be solved with 1 select in SQL Server 2005 and up.
The example of it which I always refer when I need to solve such a problem could be found in BOL: Recursive Queries Using Common Table Expressions
USE AdventureWorks;
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
0 AS Level
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
Level + 1
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Research and Development' OR Level = 0;
Another slight variation of this same theme can be found in this MSDN thread
———————
Below I describe few recent problems I solved using recursive CTE technique:
Problem definition:
I use SQL Server 2005 and I have a list of appr. 7700 items. In the list there is the complete stock quantity for each item and I also have the typical amount for each pallet. I need a list with all individual pallets and its stock
| Item | stock | amount/pallet |
|---|---|---|
| 100-001 | 2500 | 1000 |
I need the list to look like this
| 100-001 | 1000 |
| 100-001 | 1000 |
| 100-001 | 500 |
Is this possible to do?
It took me at least 20 minutes to come up with the following solution:
declare @t table (item Varchar(10), Stock money, Pallet money)
insert into @t values('100-001', 2500, 1000)
insert into @t values('100-002', 3000, 400)
insert into @t values('100-003', 2000, 400)
;with cte_toInsert as
(select Item, Pallet, Amount, Original_Stock, Stock - Amount as Stock
from (select Item, Pallet, Stock as Original_Stock, Stock, case when Stock-Pallet > 0 then Pallet else Stock end as Amount from @t) X
where Stock = 0 or (Stock >=0 and Amount > 0)
union all select Item, Pallet, Amount, Original_Stock, Stock - Amount as Stock
from (select Item, Pallet, Original_Stock, Stock,
case when Stock-Pallet > 0 then Pallet else Stock end as Amount from cte_toInsert) X
where (Stock = 0 and Amount > 0) OR Stock > 0 )
select * from cte_toInsert order by Item, Amount DESC OPTION (MAXRECURSION 10)
Here is another interesting problem where I had to use Nikola's help to come up to the final solution:
Given the initial list like this
pk_rights fk_rights RightsName RightsOrder Description ----------- ----------- -------------------------------------------------- ----------- ------------------------------ 1 NULL Open Category Library 0 Open Category Library 18 NULL Open Portfolio Footnote Library 1 Open Portfolio Footnote Library 2 1 Add Category Library 0 Add Category Library 3 1 Delete Category Library 1 Delete Category Library 11 2 Edit Category Library 0 Edit Category Library 7 2 Add Category 2 Add Category 12 2 Add Category Language 4 Add Category Language 14 2 Add Category Caption 6 Add Category Caption 9 2 Delete Category 3 Delete Category 16 2 Delete Category Caption 7 Delete Category Caption 17 2 Delete Category Language 5 Delete Category Language 8 7 Edit Category 0 Edit Category 13 12 Edit Category Lanaguage 0 Edit Category Lanaguage 15 14 EditCategory Caption 0 EditCategory Caption
I need the output to look like this.... the indents are for readability only but the rows must be ordered in this sequence.
pk_rights fk_rights RightsName RightsOrder Description ----------- ----------- -------------------------------------------------- ----------- ------------------------------ 1 NULL Open Category Library 0 Open Category Library 2 1 Add Category Library 0 Add Category Library 11 2 Edit Category Library 0 Edit Category Library 3 1 Delete Category Library 1 Delete Category Library 7 2 Add Category 2 Add Category 8 7 Edit Category 0 Edit Category 9 2 Delete Category 3 Delete Category 12 2 Add Category Language 4 Add Category Language 13 12 Edit Category Lanaguage 0 Edit Category Lanaguage 17 2 Delete Cateogry Language 5 Delete Cateogry Language 14 2 Add Category Caption 6 Add Category Caption 15 14 EditCategory Caption 0 EditCategory Caption 16 2 Delete Category Caption 7 Delete Category Caption 18 NULL Open Portfolio Footnote Library 1 Open Portfolio Footnote Library
Here is a solution by Nikola:
;with cte_t AS
(
SELECT T1.pk_rights,
t1.fk_rights,
T1.RightsName,
0 AS LEVEL,
T1.RightsOrder,
T1.Description AS ChildDescr,
CAST(RIGHT('000'+CAST(t1.RightsOrder AS VARCHAR(3)),2) AS VARCHAR(50)) AS ro
FROM @Test T1
WHERE T1.fk_rights IS NULL
UNION all
SELECT T1.pk_rights,
T1.fk_rights,
T1.RightsName,
LEVEL + 1 AS LEVEL,
T1.RightsOrder,
T1.Description AS ChildDescr,
CAST(D.ro + '-' + RIGHT('000'+CAST(t1.RightsOrder AS VARCHAR(3)),2) AS VARCHAR(50)) AS ro
FROM @Test T1
INNER join cte_t D ON T1.fk_rights = D.pk_rights
)
SELECT cte.pk_rights,
cte.fk_rights,
LEFT(REPLICATE(' ',cte.LEVEL)+cte.RightsName,50) AS RightsName,
LEFT(REPLICATE(' ',cte.LEVEL)+CAST(RightsOrder AS VARCHAR(5)),15) AS RightsOrder,
LEFT(cte.ChildDescr,50) AS [Description]
FROM cte_t cte
ORDER BY ro
Another simple problem was presented in this thread
Find sum of products which have multiple level of categories and subcategories:
-- creating test data
declare @Test table (ID int identity(1,1), Valore int, IDRef int NULL)
insert into @Test (Valore, IDRef) values (100,null), (20,1), (15,2),(-10,3), (200,null),(20,5),(335,6)
-- now creating recursive CTE
select * from @Test
; with cte as (select ID as Root, ID as IDRef, Valore, ID from @Test where IdRef IS NULL
union all
select C.Root, T.IDRef, T.Valore, T.ID from cte C inner join @Test T on T.IDRef = C.ID)
-- Our final result
select C.Root, SUM(Valore) as Total from cte C group by C.Root option (MaxRecursion 10)
There is a similar problem discussed in this thread:
and you can view very interesting solution in this blog by Itzik Ben-Gan.
*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
