CODEWITHSUNDEEP
sqlsql-servert-sqlhierarchical-data
BennoDual
BennoDual

Reputation: 6259

Select hierarchical data without using CTE

I have the following table:

CREATE TABLE [dbo].[ProductHierarchy]
(
    [ProductHierarchyID] INT NOT NULL
          PRIMARY KEY CLUSTERED IDENTITY(1, 1),
    [ProductID] INT NOT NULL ,
    [ParentProductID] INT NULL
);

with this data:

INSERT INTO [dbo].[ProductHierarchy] ([ProductID], [ParentProductID])
VALUES (1, NULL), (2, 1), (3, 1), (4, 2), (5, 4), (6, 4), (7, 4);

Now, I can do hierarchical query with a CTE like this:

WITH [CTE_Products] ([ProductID], [ParentProductID], [ProductLevel]) AS 
(
    SELECT
        [ProductID],
        [ParentProductID],
        0 AS [ProductLevel]
    FROM 
        [dbo].[ProductHierarchy]
    WHERE 
        [ParentProductID] IS NULL

    UNION ALL

    SELECT  
        [pn].[ProductID],
        [pn].[ParentProductID],
        [p1].[ProductLevel] + 1
    FROM 
        [dbo].[ProductHierarchy] AS [pn]
    INNER JOIN 
        [CTE_Products] AS [p1] ON [p1].[ProductID] = [pn].[ParentProductID]
)
SELECT  
    [ProductID],
    [ParentProductID],
    [ProductLevel]
FROM 
    [CTE_Products]
ORDER BY 
    [ParentProductID];

But how can I reach the same goal with one SQL statement without using a CTE? Is this possible?

Upvotes: 2

Views: 3392

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

CTE allows to solve this kind of recursive queries. If you know depth level in advance you could "unwind" recursive part:

SELECT p0.ProductId, p0.ParentProductID, 0 AS ProductLevel
FROM [dbo].[ProductHierarchy] p0
WHERE p0.ParentProductId IS NULL
UNION ALL
SELECT p1.ProductId, p1.ParentProductID, 1 AS ProductLevel
FROM [dbo].[ProductHierarchy] p1
JOIN (SELECT p0.ProductId, p0.ParentProductID, 0 AS ProductLevel
      FROM [dbo].[ProductHierarchy] p0
      WHERE p0.ParentProductId IS NULL) p0
  ON p1.ParentProductId = p0.ProductId
UNION ALL
SELECT p2.ProductId, p2.ParentProductID, 2 AS ProductLevel
FROM [dbo].[ProductHierarchy] p2
JOIN (SELECT p1.ProductId, p1.ParentProductID, 1 AS ProductLevel
      FROM [dbo].[ProductHierarchy] p1
      JOIN (SELECT p0.ProductId, p0.ParentProductID, 0 AS ProductLevel
      FROM [dbo].[ProductHierarchy] p0
      WHERE p0.ParentProductId IS NULL) p0
        ON p1.ParentProductId = p0.ProductId) p1
  ON p2.ParentProductId = p1.ProductId
UNION ALL
SELECT p3.ProductId, p3.ParentProductID, 3 AS ProductLevel
FROM [dbo].[ProductHierarchy] p3
JOIN  (SELECT p2.ProductId, p2.ParentProductID, 2 AS ProductLevel
       FROM [dbo].[ProductHierarchy] p2
       JOIN (SELECT p1.ProductId, p1.ParentProductID, 1 AS ProductLevel
             FROM [dbo].[ProductHierarchy] p1
             JOIN (SELECT p0.ProductId, p0.ParentProductID, 0 AS ProductLevel
                    FROM [dbo].[ProductHierarchy] p0
                    WHERE p0.ParentProductId IS NULL) p0
              ON p1.ParentProductId = p0.ProductId) p1
        ON p2.ParentProductId = p1.ProductId) p2
  ON p3.ParentProductId = p2.ProductId;

DBFiddle Demo

Upvotes: 2

Related Questions