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.

T-SQL Tuesday logo

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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
;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
;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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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;
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:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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) 
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:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
;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
;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:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
-- 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)
-- 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