Syntax for a CTE for recursive queries is : With « Transact SQL « SQL Server / T-SQL Tutorial






5>
6> CREATE TABLE airplane
7>        (containing_assembly VARCHAR(10),
8>        contained_assembly VARCHAR(10),
9>        quantity_contained INT,
10>        unit_cost DECIMAL (6,2))
11> GO
1>       insert into airplane values ('Airplane', 'Fuselage', 1, 10)
2>       insert into airplane values ('Airplane', 'Wings', 1, 11)
3>       insert into airplane values ('Airplane', 'Tail', 1, 12)
4>       insert into airplane values ('Fuselage', 'Cockpit', 1, 13)
5>       insert into airplane values ('Fuselage', 'Cabin', 1, 14)
6>       insert into airplane values ('Fuselage', 'Nose', 1, 15)
7>       insert into airplane values ('Cockpit', NULL, 1, 13)
8>       insert into airplane values ('Cabin', NULL, 1, 14)
9>       insert into airplane values ('Nose', NULL, 1, 15)
10>       insert into airplane values ('Wings', NULL, 2, 11)
11>       insert into airplane values ('Tail', NULL, 1, 12)
12> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1> --The following example shows the use of the WITH clause to define a query that calculates the total costs of each assembly.
2>
3>       WITH list_of_parts(assembly, quantity, cost) AS
4>       (SELECT containing_assembly, quantity_contained, unit_cost
5>         FROM airplane
6>         WHERE contained_assembly IS NULL
7>       UNION ALL
8>       SELECT a.containing_assembly, a.quantity_contained,
9>          CAST(l.quantity*l.cost AS DECIMAL(6,2))
10>          FROM list_of_parts l,airplane a
11>          WHERE l.assembly = a.contained_assembly)
12>       SELECT * FROM list_of_parts
13> GO
assembly   quantity    cost
---------- ----------- --------
Cockpit              1    13.00
Cabin                1    14.00
Nose                 1    15.00
Wings                2    11.00
Tail                 1    12.00
Cockpit              1    13.00
Cabin                1    14.00
Nose                 1    15.00
Wings                2    11.00
Tail                 1    12.00
Cockpit              1    13.00
Cabin                1    14.00
Nose                 1    15.00
Wings                2    11.00
Tail                 1    12.00
Airplane             1    12.00
Airplane             1    12.00
Airplane             1    12.00
Airplane             1    22.00
Airplane             1    22.00
Airplane             1    22.00
Fuselage             1    15.00
Fuselage             1    15.00
Fuselage             1    15.00
Airplane             1    15.00
Airplane             1    15.00
Airplane             1    15.00
Airplane             1    15.00
Airplane             1    15.00
Airplane             1    15.00
Airplane             1    15.00
Airplane             1    15.00
Airplane             1    15.00
Fuselage             1    14.00
Fuselage             1    14.00
Fuselage             1    14.00
Airplane             1    14.00
Airplane             1    14.00
Airplane             1    14.00
Airplane             1    14.00
Airplane             1    14.00
Airplane             1    14.00
Airplane             1    14.00
Airplane             1    14.00
Airplane             1    14.00
Fuselage             1    13.00
Fuselage             1    13.00
Fuselage             1    13.00
Airplane             1    13.00
Airplane             1    13.00
Airplane             1    13.00
Airplane             1    13.00
Airplane             1    13.00
Airplane             1    13.00
Airplane             1    13.00
Airplane             1    13.00
Airplane             1    13.00
Airplane             1    12.00
Airplane             1    12.00
Airplane             1    12.00
Airplane             1    22.00
Airplane             1    22.00
Airplane             1    22.00
Fuselage             1    15.00
Fuselage             1    15.00
Fuselage             1    15.00
Airplane             1    15.00
Airplane             1    15.00
Airplane             1    15.00
Airplane             1    15.00
Airplane             1    15.00
Airplane             1    15.00
Airplane             1    15.00
Airplane             1    15.00
Airplane             1    15.00
Fuselage             1    14.00
Fuselage             1    14.00
Fuselage             1    14.00
Airplane             1    14.00
Airplane             1    14.00
Airplane             1    14.00
Airplane             1    14.00
Airplane             1    14.00
Airplane             1    14.00
Airplane             1    14.00
Airplane             1    14.00
Airplane             1    14.00
Fuselage             1    13.00
Fuselage             1    13.00
Fuselage             1    13.00
Airplane             1    13.00
Airplane             1    13.00
Airplane             1    13.00
Airplane             1    13.00
Airplane             1    13.00
Airplane             1    13.00
Airplane             1    13.00
Airplane             1    13.00
Airplane             1    13.00
Airplane             1    12.00
Airplane             1    12.00
Airplane             1    12.00
Airplane             1    22.00
Airplane             1    22.00
Airplane             1    22.00
Fuselage             1    15.00
Fuselage             1    15.00
Fuselage             1    15.00
Airplane             1    15.00
Airplane             1    15.00
Airplane             1    15.00
Airplane             1    15.00
Airplane             1    15.00
Airplane             1    15.00
Airplane             1    15.00
Airplane             1    15.00
Airplane             1    15.00
Fuselage             1    14.00
Fuselage             1    14.00
Fuselage             1    14.00
Airplane             1    14.00
Airplane             1    14.00
Airplane             1    14.00
Airplane             1    14.00
Airplane             1    14.00
Airplane             1    14.00
Airplane             1    14.00
Airplane             1    14.00
Airplane             1    14.00
Fuselage             1    13.00
Fuselage             1    13.00
Fuselage             1    13.00
Airplane             1    13.00
Airplane             1    13.00
Airplane             1    13.00
Airplane             1    13.00
Airplane             1    13.00
Airplane             1    13.00
Airplane             1    13.00
Airplane             1    13.00
Airplane             1    13.00

(141 rows affected)
1>
2> drop table airplane;
3> GO
1>








20.25.With
20.25.1.Common Table Expressions
20.25.2.Select from Common Table Expressions
20.25.3.Syntax for a CTE for recursive queries is
20.25.4.With as