SQL query to discover levels underneath

Question

I have a table structure as below. This is only a selected set of data that are directly(A,B,C,D and E) and indirectly ('B1', 'B2', 'C1', 'C2', 'C3', 'B21' and 'C31') related to 'X', to explain the question. The actual table has loads more records that have nothing to do with this 'X'

(For example there can be another Item 'Y' on the table that is made up of a 'N', 'K', 'A', 'B1', 'B21' and 'C31')

Table1

Parent ChildP  SubCat  Qty
X      A       0       2
X      B       1       1
X      C       1       2
X      D       0       1
X      E       0       1
B      B1      0       1
B      B2      1       1
C      C1      0       1
C      C2      0       1
C      C3      1       1
B2     B21     0       1
C3     C31     0       1
Y      N       0       1
Y      K       1       2
Y      A       0       2
Y      B1      0       1
Y      B21     0       1
Y      C31     0       1
K      K1      0       0

I will take Parent 'X' as the example for this question. Below is sorted to show only where Parent = 'X'

Table1 WHERE Parent = 'X'

Parent ChildP SubCat  Qty
X       A       0      2
X       B       1      1
X       C       1      2
X       D       0      1
X       E       0      1

Now in this table where ChildPs with SubCat as 1 has their own childPs underneath.

Ex: SELECT * FROM Table1 WHERE Parent = 'B'

Parent ChildP SubCat Qty
B      B1     0       1
B      B2     1       1

Ex: SELECT * FROM Table1 WHERE Parent = 'C'

Parent ChildP SubCat Qty
C      C1     0       1
C      C2     0       1
C      C3     1       1

What I want my result set to look like is something like below, showing everything that X is made up from not just the top level ChildPs,

Parent Level FROM  ChildP SubCat  Qty
X      1     X     A      0       2
X      1     X     B      1       1
X      1     X     C      1       2
X      1     X     D      0       1
X      1     X     E      0       1
X      2     B     B1     0       1
X      2     B     B2     1       1
X      2     C     C1     1       1
X      2     C     C2     0       1
X      2     C     C3     0       1

These B2 and C1 will have further children underneath them and these need showing as well, and ideally the final result would look like below:

Parent Level FROM  ChildP SubCat  Qty
X      1     X     A      0       2
X      1     X     B      1       1
X      1     X     C      1       2
X      1     X     D      0       1
X      1     X     E      0       1
X      2     B     B1     0       1
X      2     B     B2     1       1
X      2     C     C1     1       1
X      2     C     C2     0       1
X      2     C     C3     0       1
X      3     B2    B2A    0       1
X      3     C1    C1A    0       1

Now this B2A and C1A might have further levels underneath, down to about 5-6 levels. My issue is, that I would not know how many levels each ChildP goes to initially looking at a Parent (Ex:X)

It's like saying: Show everything where Parent is X and having an if clause to say if SubCat is '1' look further until SubCat is '0'

Is this possible with a SQL Query ?

Possible duplicate of [Sql server CTE and recursion example](http://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example) — Paddy, Dec 05 '16 at 14:12
Hi i do think that question is close to what I want but the result table its looking at is different from what I want? — Sinnerv, Dec 05 '16 at 15:00
It's maybe not a 100% answer, but you should be able to take the approach and work with it. — Paddy, Dec 05 '16 at 15:40
How do you find the root elements is it just ones where parent = X — Steve Ford, Dec 07 '16 at 14:45
@Alec.NO I will name it Origin or something.. Didn't occur to me when i was figuring out the example — Sinnerv, Dec 08 '16 at 13:21
@SteveFord Well you get the first level and these are the level 1 components. On these, if the SubCat Value is 0 it means it doesnt break down any further if it's 1 it means it will have multiple levels underneath it (up to 5 at times) Thanks — Sinnerv, Dec 08 '16 at 13:25
@Sinnerv you have asked nearly the same question in comments on all of the answers about duplicates. And the reason is likely either code related or perhaps data related. but the only way fo rthe community to understand is for you to show one or the other. Start with taking your example above writing the query that gives you duplicates and posting it here then we can see where you are having difficulty because most of the answers below will do recursion of parent child some will have different nauances than others. — Matt, Dec 08 '16 at 17:37
Looks like your hierarchy is not a tree but a network. What result do you expect for data like `('X','A',0,2), ('X','B',1,1), ('X','C',1,2), ('C','B',0,1), ('B','B1',0,1), ('B','B2',1,1)` where B1 and B2 appear at different levels ? — Serg, Dec 09 '16 at 19:10

score 4 · Answer 1 · answered Dec 07 '16 at 14:55

Here you go using a recursive CTE:

Set Up

CREATE TABLE #table1
(
   Parent VarChar(50),
   ChildP VarChar(50),
   SubCat Int,
   Qty Int
)

INSERT INTO #table1 
Values ('X', 'A', 0,2),
        ('X', 'B', 1, 1),
        ('X', 'C', 1, 2),
        ('X', 'D', 0, 1),
        ('X', 'E', 0, 1),
        ('B', 'B1', 0, 1),
        ('B', 'B2', 1, 1),
        ('C', 'C1', 1, 1),
        ('C', 'C2', 0, 1),
        ('C', 'C3', 0, 1),
        ('B2', 'B2A', 0, 1),
        ('C1', 'C1A', 0, 1)

Query

;With CTE
As
(
    SELECT t1.Parent, 1 As Level, t1.Parent As [From], t1.ChildP, t1.SubCat,t1.Qty
    FROM #Table1 t1
    WHERE t1.Parent = 'X'
    UNION ALL
    SELECT CTE.parent, CTE.Level + 1, t2.Parent, t2.ChildP, t2.SubCat,t2.Qty 
    FROM #Table1 t2
    INNER JOIN CTE 
        ON t2.Parent = CTE.ChildP
)
SELECT *
FROM CTE
ORDER BY Parent, [Level], [From]

Results

Parent  Level   From    ChildP  SubCat  Qty
     X      1      X    A            0    2
     X      1      X    B            1    1
     X      1      X    C            1    2
     X      1      X    D            0    1
     X      1      X    E            0    1
     X      2      B    B1           0    1
     X      2      B    B2           1    1
     X      2      C    C1           1    1
     X      2      C    C2           0    1
     X      2      C    C3           0    1
     X      3      B2   B2A          0    1
     X      3      C1   C1A          0    1

Hello, I've run this on a table where i have up to 5 levels and it gives me loads of duplicates. any idea why? — Sinnerv, Dec 08 '16 at 13:18
@Sinnerv please provide real sample data that demonstrates this otherwise we are just shooting in the dark! — Steve Ford, Dec 09 '16 at 10:08
@Sinnerv if you don't want duplicates, but your data contains duplicates then you could alter the Select * FROM CTE to SELECT DISTINCT * FROM CTE — Steve Ford, Dec 09 '16 at 10:17

Sefe · Accepted Answer · 2016-12-07T15:59:55.117

1

You can do a recursive SQL Server query with a common table expression (CTE):

WITH Results(Parent, Level, [FROM], ChildP, SubCat,  Qty)
AS
(
    SELECT Parent, 1 AS Level, Parent AS [FROM], ChildP, SubCat, Qty
    FROM Table1
    UNION ALL
    SELECT r.Parent, r.Level + 1 AS Level, t.Parent, t.ChildP, t.SubCat, t.Qty
    FROM Table1 t
    INNER JOIN Results r ON t.Parent = r.ChildP
)
SELECT Parent, Level, [FROM], ChildP, SubCat,  Qty
FROM Results
WHERE Parent = 'X'
ORDER BY Level

Check also the MSDN article about this issue.

To break down this particular example for a single row with a recursion step, let's assume you only have X and B in your table. The first part of the CTE (the anchor) will select the row from the table and arrange the results like this:

"Columns in Results": Parent, Level, [FROM], ChildP, SubCat, Qty
"Columns in Table1":  Parent, ./.  , Parent, ChildP, SubCat, Qty
"Values":             'X'   , 1    , 'X'   , 'B'   , 1     , 1

It will then perform a UNION ALL with a query to Table1 and a JOIN with the previous result obtained in the first part:

"Columns in Results":          Parent, Level, [FROM], ChildP, SubCat, Qty
"Columns of B (from Table1)":                 Parent, ChildP, SubCat, Qty    
"Values of B (from Table1)":                  'B'   , 'B1'  , 0     , 1
"Columns of X (from Results)": Parent, Level
"Values of X (from Results)":  'X'   , 1+1=2

With your entire data set, this will continue until all your rows are covered. That is because the second row that has been determined by recursion is also part of the result set, which is used for further recursion.

edited Dec 07 '16 at 15:59

answered Dec 07 '16 at 14:38

Sefe

13,731
5
42
55

Hello, I've run this on a table where i have up to 5 levels and it gives me loads of duplicates. any idea why? – Sinnerv Dec 08 '16 at 13:19
Sinnerv: does your DB contain duplicates? What the `WHERE Parent = 'X'` condition does is to only take the items with the parent 'X' so if you have multiple of those, they will be in the query result. What would be messing up the result completely though is you have also children with duplicate `ChildP` values. Maybe you can specify what the exact duplicates are... – Sefe Dec 08 '16 at 13:24
Well, If you query the table (Table 1) for WHERE Parent = 'X' it will bring up the Table 1 example I've given right at the top the question. So, Yes there are multiple lines for a Given 'X' on Table 1 and what I would like the query to do is identify the child elements that further break down to lower levels and display them. – Sinnerv Dec 08 '16 at 13:29
@Sinnerv: But as far is I have understood your desired result set, therese are not considered to be duplcates if each line appears once in the result set. Are these lines appearing multiple times or other lines? – Sefe Dec 08 '16 at 13:37
Contd: So for example If I had Queried Table 1 for WHERE Parent = 'B' the table would have B1 and B2 as ChildPs and Parent would be B and on the next level it needs to figure out if any of these have levels underneath.If there are any, display them. Once done, move to next level up and work through the whole list of initial ChildPs. Hope this explains. Thanks – Sinnerv Dec 08 '16 at 13:41
@Sinnerv: is your issue the ordering of the result set? If yes, remove the line `ORDER BY Level`. – Sefe Dec 08 '16 at 13:52
No its not the ordering in examples where there are up to about 4-5 levels its shows more than what there really is – Sinnerv Dec 08 '16 at 14:49
Sinnerv Can you update the sample data to include rows that generate the duplicates you are talking about? I'm sure this would help @Sefe. – David Rushton Dec 08 '16 at 17:03
@Sinnerv: Thanks, man. Did you resolve the issue with the duplicates or is it still there? – Sefe Dec 12 '16 at 12:44
Hi no its fine nothing wrong with the query. the table actually did contain duplicates. bad data!! – Sinnerv Jan 25 '17 at 13:24

SQL query to discover levels underneath

2 Answers2