September 28, 2004 at 11:28 pm
I need a leg up on producing a resultset that'll go into a treeview on a web page.
We have the following data.
DealID ParentID ----------- ----------- 1029 NULL 1030 1029 1032 1030 1033 1030 1034 1030 1035 1030 1036 1030 1037 1030 1038 1032 1039 1032 1040 1037 1031 1029 1041 1037
This is the code I've got so far. The @SplitID will eventually become a parameter to a stored procedure. It could be any of the ID numbers from the above list.
BEGIN DECLARE @SplitID int SET @SplitID = 1035 SET NOCOUNT ON SET DATEFORMAT dmy DECLARE @DealTree table ( DealID int , ParentID int , lvl varchar(50) ) DECLARE @TopID int DECLARE @DealID int DECLARE @CurrLvl int DECLARE @cnt int SET @CurrLvl = 0 SELECT @DealID = ParentID FROM dbo.Deal WHERE DealID = @SplitID WHILE @DealID IS NOT NULL BEGIN SET @TopID = @DealID SELECT @DealID = ParentID FROM dbo.Deal WHERE DealID = @DealID END INSERT INTO @DealTree SELECT @TopID , NULL , @CurrLvl FROM dbo.Deal WHERE DealID = @TopID INSERT INTO @DealTree SELECT Deal.DealID , Deal.ParentID , @CurrLvl + 1 FROM @DealTree Tree INNER JOIN dbo.Deal ON Tree.DealID = Deal.ParentID WHERE Tree.lvl = @CurrLvl SELECT @Cnt = @@ROWCOUNT SET @CurrLvl = @CurrLvl + 1 WHILE @Cnt > 0 BEGIN INSERT INTO @DealTree SELECT Deal.DealID , Deal.ParentID , @CurrLvl + 1 FROM @DealTree Tree INNER JOIN dbo.Deal ON Tree.DealID = Deal.ParentID WHERE Tree.lvl = @CurrLvl SELECT @Cnt = @@ROWCOUNT SET @CurrLvl = @CurrLvl + 1 END SELECT DealID, ParentID, Lvl FROM @DealTree END
This code works ok given any of the above deal numbers it returns the full hierarchy that is related to that deal number.
Now I find out that I have to provide the Lvl field in dotted notation so the output should look like this.
DealID ParentID Lvl ----------- ----------- -------- 1029 NULL 0 1030 1029 0.0 1031 1029 0.1 1032 1030 0.0.0 1033 1030 0.0.1 1034 1030 0.0.2 1035 1030 0.0.3 1036 1030 0.0.4 1037 1030 0.0.5 1038 1032 0.0.0.0 1039 1032 0.0.0.1 1040 1037 0.0.5.0 1041 1037 0.0.5.1
Anyone got some ideas?
--------------------
Colt 45 - the original point and click interface
September 29, 2004 at 6:01 am
Try this
BEGIN
DECLARE @SplitID int
SET @SplitID = 1035
SET NOCOUNT ON
SET DATEFORMAT dmy
DECLARE @DealTree table (
DealID int
, ParentID int
, lvl varchar(50))
DECLARE @Temp table (
DealID int
, ParentID int)
DECLARE @Level table (
DealID int
, ParentID int
, Lvl int)
DECLARE @TopID int
DECLARE @DealID int
DECLARE @cnt int
SELECT @DealID = ParentID
FROM dbo.Deal
WHERE DealID = @SplitID
WHILE @DealID IS NOT NULL
BEGIN
SET @TopID = @DealID
SELECT @DealID = ParentID
FROM dbo.Deal
WHERE DealID = @DealID
END
INSERT INTO @Temp
SELECT
@TopID
, NULL
FROM dbo.Deal
WHERE DealID = @TopID
INSERT INTO @DealTree
SELECT
DealID
, ParentID
, '0'
FROM @Temp
SELECT @Cnt = 1
WHILE @Cnt > 0
BEGIN
DELETE FROM @Level
INSERT INTO @Level
SELECT
Deal.DealID
, Deal.ParentID
, 0
FROM @Temp Tmp
INNER JOIN dbo.Deal Deal
ON Deal.ParentID = Tmp.DealID
SELECT @Cnt = @@ROWCOUNT
DELETE FROM @Temp
INSERT INTO @Temp
SELECT DealID, ParentID
FROM @Level
IF @Cnt > 0
BEGIN
UPDATE l
SET l.Lvl = (SELECT COUNT(*)
FROM @Level l2
WHERE l2.ParentID = l.ParentID
AND l2.DealID < l.DealID)
FROM @Level l
INSERT INTO @DealTree
SELECT
l.DealID
, l.ParentID
, Tree.lvl + '.' + CAST(l.Lvl as varchar)
FROM @Level l
INNER JOIN @DealTree Tree
ON Tree.DealID = l.ParentID
END
END
SELECT DealID, ParentID, Lvl
FROM @DealTree
END
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy