XML FeedsOne 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
CTEs are one of the more useful but underused enhancements to T-SQL. More developers should start using them instead of derived tables 
I wonder if CTE's (like the CTE used in solving the pallet problem) would become more readable and understandable if the logic moved to code other than SQL. SQL is not well suited to be a programming language. 
Yes, I agree, that in many cases the processing can be also done on the client. I guess in every case it depends on many different factors what would be preferable. 
nice post. 
Greetings I recently finished reading through your blog and I’m very impressed. I do have a couple queries for you personally however. Do you consider you’re thinking about doing a follow-up publishing about this? Will you be planning to keep bringing up-to-date also? 
Most what i read online is trash and copy paste but i think you offer something different. Bravo. 
Most of the times i visit a blog I notice that most blogs are amateurish.On the other hand,I could honestly say that you writting is decent and your website solid. 
Great post full of useful tips! My site is fairly new and I am also having a hard time getting my readers to leave comments. Analytics shows they are coming to the site but I have a feeling nobody wants to be first. 
Thanks for the great post, Naomi, and also for submitting this to T-SQL Tuesday! 
Quite a good read, I always enjoy informational pieces. I hope to see more like it soon!
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.