I have a table speaking to an envelope structure, The branch field speaks to a bundle structure (or organizer structure) containing things. (for this situation, there is just a single thing for every record, however for my situation there will be numerous things at each level of profundity ( think essentially about a windows traveler structure containing things) ..

I require a question that will check what number of every thing are inside the branches of the tree structure. nonetheless I have to tally all things inside a specific profundity and lower, i.e. where the profundity is more noteworthy than 4, get a check of all things of a particular kind - I will require the tallies of all organizers at level 4 with the tallies of things at that level. https://goo.gl/frpdYV

im occupied with a cte inquiry, however I think im heading in the wrong bearing.

DECLARE @tbl TABLE
  ( 
   Id int
  ,ParentId int
  ,branch varchar(100)
  ,depth int
  ,item varchar(20)
  )
INSERT  INTO @tbl
        ( Id, ParentId,branch, depth,item )
        VALUES  (1, NULL,   '1',0,Null),
                (2, 1,  '1,2',1,NULL),
                (3, 1,  '1,2,3',2,NULL),
                (4, 3,  '1,2,3,4',3,NULL),
                (5, 4,  '1,2,3,4,5',4,NULL),
                (6, 5,  '1,2,3,4,5,6',5,'car'),
                (7, 6,  '1,2,3,4,5,6,7',6,'bus'),
                (8, 7,  '1,2,3,4,5,6,7,8',7,'truck'),
                (9, 8,  '1,2,3,4,5,6,7,8,9',8,'car'),
                (10,8,  '1,2,3,4,5,6,7,8,10',9,'bike'),
                (11,5,  '1,3,4,5,11',4,'car'),
                (12,5,  '1,3,4,5,12',4,'truck'),
                (13,4,  '1,2,3,4,13',4,'truck'),
                (14,8,  '1,2,3,4,5,6,7,8,14',8,'bike'),
                (15,8,  '1,2,3,4,5,6,7,8,15',8,'bus');

The data is probably incorrect. but essentially the result (if you only wanted the count of cars) would take all items (and their children) and count the cars.

i.e.

branch     car
'1,2,3,4'   3
'1,3,4,5'   0

asked 03 May '18, 02:34

Bargavi's gravatar image

Bargavi
(suspended)
accept rate: 0%

edited 03 May '18, 04:07

Volker%20Barth's gravatar image

Volker Barth
39.9k360547817

Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×19
×18

question asked: 03 May '18, 02:34

question was seen: 1,049 times

last updated: 03 May '18, 04:07