Creating unlimited Nested categories via Stored Procedure

I am trying to create a loop in stored procedure to forumulate a parent/chil
d
( unlimited relationships ). I do not know how to go about doing this in a
stored procedure. I can do it on the frontend but I rather not do it this
way. The following is a sample of the database table:
Categories
---
| CATID | NAME | ParentId |
---
1 Electronics 0
2 Computers 1
3 Cameras 1
4 Sony Cameras 3
5 Clothing 0
6 White TShirt 5
I would like the output to like the following
Electronics
Electronics -> Computers
Electronics -> Cameras
Electronics -> Cameras -> Sony Cameras
Clothing
Clothing -> White TShirt
How can i get this to work via a stored procedure?Something like this would work...
Drop Table #temp
Create Table #temp
(
CatID int,
[Name] varchar(20),
ParentID int
)
insert into #temp values (1, 'Electronics', 0)
insert into #temp values (2, 'Computers', 1)
insert into #temp values (3, 'Cameras', 1)
insert into #temp values (4, 'Sony Cameras', 3)
insert into #temp values (5, 'Clothing', 0)
insert into #temp values (6, 'White TShirt', 5)
Drop Table #temp2
Create Table #temp2
(
CatID int,
[Name] varchar(255),
ParentID int
)
Insert Into #temp2
Select CatID, [Name], ParentID
>From #temp
While @.@.Rowcount > 0
Update t
Set [Name] = t1.[Name] + ' -> ' + t.[Name],
ParentID = t1.ParentID
From #temp2 t Inner Join #temp t1
On t.ParentID = t1.CatID
Select * From #temp2
HTH
Jason|||Procedure nesting is limited to 32 levels in SQL Server (the same goes for
triggers). If your requirements exceed this limit, you'll have to create the
hierarchy in your application.
ML|||Loops!!' Procedural code' !! God! How non-relational!
Get a copy of TREES & HIERARCHIES IN SQL and look up the Nested sets
model.

Creating Parent/Child table with Sum for each group

Hi,
i've been working on this report for the whole day now and have come
to the conclusion that this can't be accieved with Reporting Services
SP1.
Or maybe i'm wrong...
I'm trying to display a report like this:
-- Group 1
-- SubGroup 1,1
-- SubGroup 1,2
-- SubGroup 1,3
-- SubSubGroup 3,1
-- SubSubSubGroup 3,1,1
-- Totals for SubSubSubGroup 3,1,1
-- Total for SubSubGroup 3,1
-- Totals for SubGroup 1,3
-- Total for Group 1
My data looks like this:
Text, Amount, Group, ParentGroup
I have done it like described here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_structure_objects_v1_3cok.asp
The problem is getting the Total to display correctly. I have tried
adding a table footer but this will only display the Total for the
entire table and not for each group.
Anybody know a solution to making this kind of report?
-MartinI had a similar problem. In the end I got around the problem by
creating a query that returned the hierarchised sub totals in the
dataset...this is relatively easy if your datasource is OLAP -
admittedly not so ideal if you are using relational datasources ...|||I found a way to work around it, and almost getting the desired result.
The result looks like this:
-- Group # 1, Total Amount
-- SubGroup # 1.1, Total Amount
-- SubGroup # 1.1.1, Total Amount
-- Element # 1.1.1.1, Amount
-- Element # 1.1.1.2, Amount
-- SubGroup # 1.1.2, Total Amount
-- Element # 1.1.2.1, Amount
I have added a Columns, "Headline", which tells me if a Row is a group
or an Element.
Then when i want the Amount displayed i use this expression:
=IIF(Fields!Headline.Value = 0, Fields!Amount.Value,
(Sum(Fields!Amount.Value, "table2_Details_Group", recursive)))
This seems to be the only way to calculate the Totals for a recursive
table...
-Martin

Creating Parent Child Hierarchies

Hi
I have the following in my cube
Location Dimension containing LocationID and LocationName attributes.
Customer Dimension containing CustomerID,CustomerName....,LocationID
The LocationID in the Location Dimension and Customer Dimension are related to each other.
I want to create a hierarchy with the LocationName in the First Level and the CustomerName in the second level. How to I accomplish this? As i said I already have a relationship defined between the two tables.

Thanks and Regards
Guruprasad Karnik

This is not a recursive hierarchy from what you are describing.

You can find a recursive hierarchy in the employee table in the Adventure works sample data base that is a part of the SQL server 2005 installation. You use parent-child hierarchies with this type of primary key-foreign key relationship in the same table.

If you download the performance guide for SSAS2005 (see the first post in this forum) and have a look at page 82 and further(Referenced relationsships), you will find a lot of helpful information.

HTH

Thomas Ivarsson