It is unfortunate that Common Table Expressions (CTEs) are seldom referenced by introductory books to SQL Server. CTEs can greatly simplify the task of writing and maintaining SQL queries, views, stored procedures, and functions.
Occasionally, situations arise where a recursive query is required to yield a complete data set such as traversing a table to determine a bill of materials for a given set of part numbers. In other words, if you’re working with hierarchical data tables then knowing CTEs is a necessity.
In our case, a single database table contains all of the part numbers Manufactured (M) and/or Purchased (P) by the company. This same table is comprised of many columns including the subcomponents required to produced a given part. For our purposes, a simplified table version appears as follows:
| ID | Part Number (PN) | SubComponent (SC) | SKUType |
|---|---|---|---|
| 1 | C1 | R2 | M |
| 2 | C2 | R2 | M |
| 3 | C3 | R3 | M |
| 4 | PA | C1 | M |
| 5 | PA | C2 | M |
| 6 | PA | C3 | M |
| 7 | PB | R1 | M |
| 8 | PC | C2 | M |
| 9 | R1 | NULL | P |
| 10 | R2 | NULL | P |
| 11 | R3 | NULL | P |
The Bill of Materials to Produce Part Number “PA” can be derived from the Bill Of Materials table shown above as follows:
- Bill of Materials
- 1 > PA
- 2 > C1
- 3 > R2
- 2 > C2
- 3 > R2
- 2 > C3
- 3 > R3
- 2 > C1
Extracting this Bill of Materials from the table is the topic of CTEs and the purpose of creating this post. First, we’ll create a sample database using the following code (T-SQL):
-- Recreate Table
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[BillOfMaterials]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[BillOfMaterial];
-- Define Schema
CREATE TABLE BillOfMaterial
(
id INTEGER IDENTITY NOT NULL,
PartNumber VARCHAR(30) NOT NULL,
PartDescription VARCHAR(50),
SubComponent VARCHAR(30),
SubDescription VARCHAR(50),
SKUType char(1) NOT NULL,
Created datetime DEFAULT GETDATE()
PRIMARY KEY (id)
);
-- Add Rows to Table
INSERT INTO BillOfMaterial (PartNumber, PartDescription, SubComponent, SubDescription, SKUType)
VALUES ('PA', 'Assembly A' , 'C1', 'Component 1' , 'M'),
('PB', 'Stamping B' , 'R1', 'Coil Steel R1' , 'M'),
('PC', 'Assembly C' , 'C2', 'Component 2' , 'M'),
('C1', 'Component 1' , 'R2', 'Coil Steel R2' , 'M'),
('R1', 'Coil Steel R1' , NULL, NULL , 'P'),
('R2', 'Coil Steel R2' , NULL, NULL , 'P'),
('PA', 'Assembly A' , 'C2', 'Component 2' , 'M'),
('PA', 'Assembly A' , 'C3', 'Component 3' , 'M'),
('C2', 'Component 2' , 'R2', 'Coil Steel R2' , 'M'),
('C3', 'Component 3' , 'R3', 'Coil Steel R3' , 'M'),
('R3', 'Coil Steel R3' , NULL, NULL , 'P');
-- Table Completed
Run a simple query to check the contents of the table.
SELECT *
FROM BillOfMaterial
ORDER BY PartNumber, SubComponent
The above query yields the following results:
Now that we have our data table, we can determine the purchased materials (‘P’) required to produce a given part. To determine the Bill of Materials for PA, the following CTE will yield the required results:
-- SemiColon ensures prior statements are terminated or CTE won't execute!
;WITH BOMcte AS (
SELECT id, PartNumber, PartDescription, SubComponent, SubDescription, SKUType, Created, 1 as lvl
FROM BillOfMaterial
WHERE PartNumber = 'PA'
UNION ALL
SELECT bom.id, bom.PartNumber, bom.PartDescription, bom.SubComponent, bom.SubDescription, bom.SKUType, bom.Created, lvl + 1
FROM BillOfMaterial AS bom
INNER JOIN BOMcte
ON BOMcte.SubComponent = bom.PartNumber
)
--SELECT Distinct
SELECT lvl, PartNumber, PartDescription, SubComponent, SubDescription, SKUType, Created
FROM BOMcte
WHERE SubComponent IS NULL
ORDER BY lvl, PartNumber
OPTION (MAXRECURSION 10)
-- MAXRECURSION 0 = "Infinite", Default = 100, Max 32767
GO
Executing the above code on the database yields the solution set as pictured below:
Books: “SQL Server Common Table Expressions” by Steve Stedman (May 2013) is a comprehensive and well-written book that discusses all aspects of CTEs and is highly recommended reading. (http://www.stevestedman.com)
Videos: SQL Server – Common Table Expressions is an excellent “must-watch” video by Steve Stedman that introduces CTE’s and advances to more practical applications of CTEs including recursion.
Platforms: SQL Server (T-SQL). Other platforms support CTEs, however, the syntax may vary from our example above.
Web References / Credits:
- http://www.SteveStedman.com
- http://www.sqlshack.com – Why is my CTE so slow?
- https//:justcheckingonall.wordpress.com How to create table in MSSSQL only if it does not exist
- http://www.blogsqlauthority.com – SQL Server – Simple Example of Recursive CTE
