Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2008
    Posts
    3

    Question Unanswered: Recursive Complete Path on SQL SERVER 2005 ???

    Dear,

    I'm having this table:

    PathID ParentPathID Path
    1 NULL D:
    2 1 Sections
    3 2 Bin
    4 3 Data
    5 4 FinancialReport.doc
    6 4 DebtReport.doc
    7 3 db.dll

    I would like to create a store procedures that will return me a full path by passing a PathID

    I started with this code:

    DECLARE @path_id int
    SET @path_id = 5;
    WITH fullPath (PathID, ParentPathID, Path)
    AS
    (
    SELECT PathID, ParentPathID, Path
    FROM tblPath WHERE PathID = @path_id
    UNION ALL
    SELECT tblPath.PathID, tblPath.ParentPathID, tblPath.Path AS Path
    FROM tblPath
    JOIN fullPath ON tblPath.PathID = fullPath.ParentPathID
    )
    SELECT * FROM fullPath

    This code will return this:
    1 NULL D:
    2 1 Sections
    3 2 Bin
    4 3 Data
    5 4 FinancialReport.doc

    What I would like to get is something like this:
    D:/Sections/Bin/Data/FinancialReport.doc

    Any help would be really appreciated. Lost too much time on it already.
    Thanks,
    pharvey

  2. #2
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    Pharvey:

    I found these suggestions:

    http://milambda.blogspot.com/2005/07...r-monkeys.html

    http://www.sqlservercentral.com/arti...rver2005/1760/

    Try searching the web for keywords such as:
    "sql server"
    "hierarchy"
    "nested"
    "parentid"

    Good luck I hope this helps
    -Reghardt

  3. #3
    Join Date
    Apr 2008
    Posts
    3
    Thanks a lot Reghardt but if someone have a solution more concrete that would help. Those link are generating something similar than the results I already get. I would to retrieve one row with the path not many rows.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Does this help:
    Code:
    IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = object_id('dbo.org')) BEGIN
        DROP TABLE dbo.org
    END
    GO
    
    CREATE TABLE dbo.org (
       child       varchar(15) NOT NULL PRIMARY KEY
     , description varchar(20)     NULL
     , parent      varchar(15)     NULL
    )
    
    INSERT INTO dbo.org (child, parent, description)
          SELECT '01', NULL, 'Company One'
    UNION SELECT '02', NULL, 'Company 2'
    UNION SELECT '03', '01', 'Operations'
    UNION SELECT '04', '01', 'HR'
    UNION SELECT '05', '02', 'ICT & Finance'
    UNION SELECT '06', '03', 'ICT'
    UNION SELECT '07', '03', 'Finance'
    UNION SELECT '08', '06', 'Analyst Team'
    UNION SELECT '09', '06', 'Support Team'
    UNION SELECT '10', '06', 'Development Team'
    
    DECLARE @cSearch char(50)
    SET @cSearch = 'ICT'
    
    ; WITH getSubordinates (child, parent, description) AS 
        -- Anchor query
        (SELECT child, parent, description
         FROM   dbo.org
         WHERE  description = @cSearch 
         UNION ALL
        -- Recursive query
         SELECT kids.child, kids.parent, kids.description
         FROM   dbo.org kids 
          INNER
           JOIN getSubordinates
             ON getSubordinates.child = kids.parent
        )         
    
    SELECT description FROM getSubordinates
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    And because I know Rudy will be along shortly, here are the results.
    Code:
    description          
    -------------------- 
    ICT
    Analyst Team
    Support Team
    Development Team
    
    (4 row(s) affected)
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I can't explain why the intermediate temp table is required but the variable cannot be assigned directly from the CTE.

    Code:
    IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.mytable')) BEGIN
        DROP TABLE dbo.mytable
    END
    
    IF OBJECT_ID('tempdb..#fullpath') IS NOT NULL BEGIN
        DROP TABLE #fullpath
    END
    
    CREATE TABLE dbo.mytable
        (
            PathID            INT                NOT NULL
            , ParentPathID    INT                NULL
            , [Path]        VARCHAR(100)    NOT NULL
            , CONSTRAINT pk_mytable                    PRIMARY KEY CLUSTERED (PathID) WITH (FILLFACTOR = 100)
        )
    GO    
    
    INSERT INTO dbo.mytable (PathID, ParentPathID, [Path])
    SELECT    1, NULL, 'D:'
    UNION ALL SELECT    2, 1, 'Sections'
    UNION ALL SELECT    3, 2, 'Bin'
    UNION ALL SELECT    4, 3, 'Data'
    UNION ALL SELECT    5, 4, 'FinancialReport.doc'
    UNION ALL SELECT    6, 4, 'DebtReport.doc'
    UNION ALL SELECT    7, 3, 'db.dll'
    
    DECLARE    @full_path    AS VARCHAR(100)
            , @path_id    AS INT
    
    SELECT    @path_id = 5;
    WITH fullPath (PathID, ParentPathID, Path)
    AS
    (
        SELECT PathID, ParentPathID, Path
        FROM mytable WHERE PathID = @path_id
        UNION ALL
        SELECT mytable.PathID, mytable.ParentPathID, mytable.Path AS Path
        FROM mytable
        JOIN fullPath ON mytable.PathID = fullPath.ParentPathID
    )
    SELECT    * INTO #fullpath
    FROM    fullPath
    
    SELECT    @full_path    = path + COALESCE('\' + @full_path, '') 
    FROM    #fullpath
    ORDER BY PathID DESC
    
    SELECT    @full_path
    
    IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.mytable')) BEGIN
        DROP TABLE dbo.mytable
    END
    IF OBJECT_ID('tempdb..#fullpath') IS NOT NULL BEGIN
        DROP TABLE #fullpath
    END
    Rudy Clause: the result is as per post #1, but using '\' not '/'.

    SELECT * INTO is not good practice but I don't have all day to type
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2007
    Posts
    183

    Temp table not needed

    Code:
    DECLARE	@myTable TABLE
    	(
    		PathID            INT                NOT NULL
    		, ParentPathID    INT                NULL
    		, [Path]        VARCHAR(100)    NOT NULL
    	)
    
    INSERT	@mytable (PathID, ParentPathID, [Path])
    SELECT    1, NULL, 'D:'
    UNION ALL SELECT    2, 1, 'Sections'
    UNION ALL SELECT    3, 2, 'Bin'
    UNION ALL SELECT    4, 3, 'Data'
    UNION ALL SELECT    5, 4, 'FinancialReport.doc'
    UNION ALL SELECT    6, 4, 'DebtReport.doc'
    UNION ALL SELECT    7, 3, 'db.dll'
    
    
    DECLARE    @path_id    AS INT
    
    SET    @path_id = 5;
    
    WITH Yak (PathID, FullPath)
    AS
    (
        SELECT ParentPathID, cast('\' + Path as varchar(max)) AS Path
        FROM @mytable WHERE PathID = @path_id
    
        UNION ALL
    
        SELECT s.ParentPathID, cast('\' + s.Path as varchar(max)) + y.fullpath
        FROM @mytable AS s
        INNER JOIN Yak AS y ON y.PathID = s.PathID
    
    )
    
    SELECT  substring(fullpath, 2, 8000)
    FROM    Yak
    where	pathid is null

  8. #8
    Join Date
    Apr 2007
    Posts
    183
    You can even fetch multiple file paths at once
    Code:
    ;WITH Yak (PathID, FullPath)
    AS
    (
        SELECT ParentPathID, cast('\' + Path as varchar(max)) AS Path
        FROM @mytable WHERE PathID in (5,6,7)
    
        UNION ALL
    
        SELECT s.ParentPathID, cast('\' + s.Path as varchar(max)) + y.fullpath
        FROM @mytable AS s
        INNER JOIN Yak AS y ON y.PathID = s.PathID
    
    )
    
    SELECT  substring(fullpath, 2, 8000)
    FROM    Yak
    where	pathid is null

  9. #9
    Join Date
    Apr 2008
    Posts
    3
    Ok guys, found this function wich is doing the job i was searching for:

    CREATE FUNCTION [dbo].[slFullPath](@PathID int)
    RETURNS nvarchar(4000)
    AS
    BEGIN
    DECLARE @NewPath nvarchar(4000)

    DECLARE @ParentPathID int
    DECLARE @ParentPath nvarchar(260)

    SELECT @ParentPathID = ParentPathID, @NewPath = Path FROM tblPath WHERE PathID = @PathID
    WHILE @ParentPathID IS NOT NULL
    BEGIN
    SELECT @ParentPathID = ParentPathID, @ParentPath = Path FROM tblPath WHERE PathID = @ParentPathID
    SELECT @NewPath = @ParentPath + '\' + @NewPath
    END

    RETURN @NewPath
    END

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Peter - much better!
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •