Hope you can help me out over here. We are building a datamart for a large group of users. They will use BOE webi's to generate reports with. One of the tables is a category structure (see picture below).The strongest requirement is that they will be able
to aggregate information on any level -including -all- sublevels.
For that to work, we need to write a recursive query that outputs all categories and all of their children (and grandchildren). So per "branch" if you will. I knew how to write that recursion in .Net but not how to do it in SQL. Before SQLUSA
& Ramireddy's repsonses I had no clue how to implement that with CTE. -So thanks you guys!
I've added my solution below. The problem i am running into now is the maxrecursion. When using the CTE query below on the actual database it runs into the limitation of 32767 iterations. Anyone who can help me solve this, using the example below?
Sample Table SQL
If exists (select top 1 * from sysobjects where xtype='U' and name = 'Category')
Drop Table Category
Create Table Category(
CategoryId Numeric(18,0) Null,
ParentCategoryId Numeric(18,0) Null,
Insert into Category(CategoryId,ParentCategoryId) Values (1,0)
Insert into Category(CategoryId,ParentCategoryId) Values (2,1)
Insert into Category(CategoryId,ParentCategoryId) Values (3,2)
Insert into Ca
View Complete Post