on 2018 May 03 3:34 AM
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
Request clarification before answering.
User | Count |
---|---|
87 | |
10 | |
9 | |
8 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.