Here is a stored procedure I used for an old forum app. Should work for
your situation.
--DROP TABLE #stack
--DROP TABLE #x
--DROP TABLE #y
--CREATE PROCEDURE expand (@current int) as
DECLARE @current int
DECLARE @level int, @line char(20)
DECLARE @Pos int
DECLARE @PrevReplyTo int
CREATE TABLE #stack (item int, Thislevel int)
CREATE TABLE #x (ID int, Name varchar(256), DateCreated datetime, InReplyTo
int, Topic int, TotalReply int, Creator int, IsTop bit, Root int, TopDate
datetime, _USER_ID int, MessageFrom varchar(256),ThisLevel int, Position
int)
CREATE TABLE #y (ID int, InReplyTo int, Root int)
SET @current = 94
SET @Pos = 0
SET @PrevReplyTo = @current
INSERT #y
SELECT ID,InReplyTo, Root
FROM Threads
WHERE Root = @current
ORDER BY DateCreated DESC
INSERT #x
SELECT Threads.ID AS "ID",
Threads.Name AS "Name",
Threads.DateCreated AS "DateCreated",
Threads.InReplyTo AS "InReplyTo",
Threads.Topic AS "Topic",
Threads.TotalReply AS "TotalReply",
Threads.Creator AS "Creator",
Threads.IsTop AS "IsTop",
Threads.Root AS "Root",
Threads.TopDate AS "TopDate",
Users.ID AS "_USER_ID",
Users.Userid AS "MessageFrom",
0,
0
FROM Threads,Users
WHERE Threads.Root = @current AND Threads.Creator = Users.ID
SET NOCOUNT ON
INSERT INTO #stack VALUES (@current, 1)
SELECT @level = 1
WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE Thislevel = @level)
BEGIN
-- If an item on the stack exists at this level, grab it
SELECT @current = item FROM #stack WHERE Thislevel = @level
-- Increment the position
SET @Pos = @Pos + 1
-- Update the table with the new position
UPDATE #x SET Position = @Pos, ThisLevel = @level - 1 WHERE ID =
@current
-- Delete this entry from the temp table (#y)
DELETE FROM #y WHERE ID = @current
-- Delete this entry from the stack
DELETE FROM #stack WHERE Thislevel = @level AND item = @current
-- Insert into the stack any IDs that are replies to the CURRENT ID
INSERT #stack SELECT ID, @level + 1 FROM #y WHERE InReplyTo =
@current
-- If any were found, increment the level
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
IF ((SELECT COUNT(*) FROM #y) > 0 AND (SELECT COUNT(*) FROM #stack) = 0)
BEGIN
IF @level = 0
SELECT @level = @level + 1
INSERT #stack
SELECT ID, @level + 1 FROM #y
SELECT * FROM #stack
END
END -- WHILE
DROP TABLE #stack
SELECT ID,Name,ThisLevel,Position FROM #x ORDER BY Position,DateCreated
DROP TABLE #x
DROP TABLE #y
----------------------------------------------
Original Message
From: "Hagan, Ryan Mr (Contractor ACI)"<[EMAIL PROTECTED]>
Subject: RE: SQL syntax for Supertypes-Subtypes AKA Circular Reference AKA
Entity Tree
Date: Wed, 27 Aug 2003 11:21:52 -0400
>I asked a very similar question a few weeks ago. One solution was posted
by
>Jochem van Dieten:
>
>Use a nested set model, or use a 2 table model where one table holds
>complete trees in an XMLData field (which you only need to edit when a
>new element is inserted) and one holds individual messages but with an
>FK to the table that holds the complete trees in XML.
>I usually prefer the first solution, but in some cases the latter
>solution allows you to offload the whole thing to the client and let the
>client figure it out using XSLT. Something like:
>http://spike.oli.tudelft.nl/jochemd/test/tree/test.xml
>
>
>If you want more info, check out the archives. The thread was:
>
>Re: recursion in cold fusion ( was RE: Creating a list with infinite
>groupings and indents )
>
>
>-----Original Message-----
>From: Blum, Jason (SAA) [mailto:[EMAIL PROTECTED]
>Sent: Wednesday, August 27, 2003 11:13 AM
>To: CF-Talk
>Subject: SQL syntax for Supertypes-Subtypes AKA Circular Reference AKA
>Entity Tree
>
>
>Hello,
>
>Here's a puzzle for all you 'Joe Celko' types:
>
>Am trying to figure out how to do on SQL Server something I can do in
>Oracle. I think this problem is variously known as Subtypes-Supertypes,
>Circular Reference, Entity Tree, etc...
>
>Given this data:
>
>Columns: iEntityID, iSubEntityOf, sName
>1 1 Mary
>2 2 1 Fritz
>3 3 1 John
>4 4 2 Abu
>5 5 2 Ludwig
>6 6 3 Abigail
>7 7 3 Josef
>8 8 6 Mark
>9 9 6 Ben
>10 10 6 Habib
>11 11 9 Paul
>12 12 11 Mahatma
>
>I want to graphically represent the tree in this data - i.e. Mary is the
>boss; Fritz and John report to Mary; Abu and Ludwig report to Fritz;
>etc...
>
>The important thing is that the SubEntityOf column is kind of a foreign
>key to the primary key EntityID, such that the tree can be infinitely
>deep.
>
>I used to be able to do this in Oracle using:
>
><CFQUERY NAME="GetEntities" DATASOURCE="MyDatasource">
> SELECT iEntityID, iSubEntityOf, sName
> FROM Mytable
> WHERE NOT iEntityID
> START WITH iEntityID=1
> CONNECT BY PRIOR iEntityID=iSubEntityOf
></CFQUERY>
>
>But I can't use these functions in SQL Server...
>
>Thanks!
>
>-Jason
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/lists.cfm?link=t:4
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community.
http://www.fusionauthority.com/ads.cfm
