CODEWITHSUNDEEP
sql-servercommon-table-expression
Dominota
Dominota

Reputation: 151

How to convert SQL Server CTE query to normal query

I have a DeviceCategory table like this

enter image description here

In the table, the CategoryID column is a child of CategoryParentCode. I want to query to get all children of a parent.

This task can do by using a CTE query:

WITH CTE_DEVICE_CATEGORY (CID, CParentCode, CName, CCode, CDescription, EmpLevel) AS
(
    SELECT 
        CategoryID, CategoryParentCode, CategoryName, CategoryCode, 
        CategoryDescription, 1
    FROM
        DeviceCategory
    WHERE 
        CategoryParentCode = 0

    UNION ALL

    SELECT 
        e.CategoryID, e.CategoryParentCode, e.CategoryName, e.CategoryCode, 
        e.CategoryDescription, r.EmpLevel + 1
    FROM 
        DeviceCategory e
    INNER JOIN 
        CTE_DEVICE_CATEGORY r ON e.CategoryParentCode = r.CID
)
SELECT
    CID, CParentCode, CName, CCode, CDescription
FROM 
    CTE_DEVICE_CATEGORY 
ORDER BY 
    EmpLevel, CParentCode

I don't want to use WITH AS so I have a question is how to convert such CTE query to a normal query (that does not use CTE)

Can everybody help me?

Thank you in advance,

Upvotes: 1

Views: 888

Answers (1)

Antonio Tolusic
Antonio Tolusic

Reputation: 186

You could make use of temp table and a while loop. For example:

Sample data:

CREATE TABLE DeviceCategory(CategoryID int, CategoryParentCode int, CategoryName nvarchar(50), CategoryCode int, CategoryDescription nvarchar(100));

INSERT INTO DeviceCategory(CategoryID , CategoryParentCode , CategoryName , CategoryCode , CategoryDescription)
SELECT  10, 0,  'k',    7,  'fvvfv'
UNION ALL SELECT    2,  10, 'b',    4,  NULL
UNION ALL SELECT    3,  3,  'c',    NULL,   NULL
UNION ALL SELECT    4,  9,  'd',    NULL,   NULL
UNION ALL SELECT    5,  1,  'e',    NULL,   NULL
UNION ALL SELECT    6,  10, 'f',    NULL,   NULL
UNION ALL SELECT    7,  4,  'g',    NULL,   NULL
UNION ALL SELECT    8,  8,  'h',    NULL,   NULL
UNION ALL SELECT    9,  6,  'i',    NULL,   NULL

Query:

CREATE TABLE #temp (CategoryID int, CategoryParentCode int, CategoryName nvarchar(50), CategoryCode int, CategoryDescription nvarchar(50), EmpLevel int);

DECLARE @i int=1;

INSERT INTO #temp(CategoryID, CategoryParentCode, CategoryName, CategoryCode, CategoryDescription, EmpLevel)
SELECT dc.CategoryID, dc.CategoryParentCode, dc.CategoryName, dc.CategoryCode, dc.CategoryDescription, @i
FROM DeviceCategory dc 
WHERE CategoryParentCode = 0;

WHILE @i>0
begin
    INSERT INTO #temp(CategoryID, CategoryParentCode, CategoryName, CategoryCode, CategoryDescription, EmpLevel)
    SELECT dc.CategoryID, dc.CategoryParentCode, dc.CategoryName, dc.CategoryCode, dc.CategoryDescription, @i+1
    FROM DeviceCategory dc INNER JOIN
    #temp tmp ON tmp.CategoryID=dc.CategoryParentCode
    WHERE tmp.EmpLevel=@i;

    IF @@ROWCOUNT=0
    begin
        SET @i=0;
    end
        ELSE 
    begin
        SET @i+=1;
    end

end

SELECT t.CategoryID, t.CategoryParentCode, t.CategoryName, t.CategoryCode, t.CategoryDescription
FROM #temp t
ORDER BY t.EmpLevel, t.CategoryParentCode

DROP TABLE IF EXISTS #temp;

Let me know if this works for you.

EDIT: You could also use table variable @temp instead of #temp if the number of rows is not very big.

Upvotes: 2

Related Questions