'START WITH' equivalent expression in MS-SQL

Question

Oracle SQL supports START WITH expression. For instance,

CREATE VIEW customers AS
SELECT LEVEL lvl, customer_code, customer_desc, customer_category 
FROM customers_master
START WITH some_column = '0'
CONNECT BY PRIOR CUSTOMER_CODE = PARENT_CUSTOMER_CODE;

If a table contains hierarchical data, then you can select rows in a hierarchical order using hierarchical query clauses.

START WITH specifies the root row(s) of the hierarchy.

CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy.

Is there an equivalent expression for MS-SQL ?

score 3 · Accepted Answer · answered Mar 21 '18 at 11:08

Not directly. You can use recursive CTE (common table expressions) like the following, which requires a little bit more of writing:

;WITH RecursiveCTE AS
(
    -- Anchor (START WITH)
    SELECT
        customer_code, 
        customer_desc, 
        customer_category,
        Level = 1
    FROM
        customers_master AS C
    WHERE
        C.some_column = '0'

    UNION ALL

    -- Recursive join
    SELECT
        C.customer_code, 
        C.customer_desc, 
        C.customer_category,
        Level = R.Level + 1
    FROM
        RecursiveCTE AS R -- Note that we are referencing a table that we are just declaring as CTE
        INNER JOIN customers_master AS C ON
            R.CUSTOMER_CODE = C.PARENT_CUSTOMER_CODE
)
SELECT
    R.*
FROM
    RecursiveCTE AS R

Will need to review the recursive join's columns a little bit, but you should get the idea.

'START WITH' equivalent expression in MS-SQL

1 Answers1