update full path for record

  • shogunSQL

    SSChasing Mays

    Points: 643

    Hi,

    I have a table as you can see in codes below. Can you please help me with generating the last column 'ExpectingResultofPath'?

    Considerations:

    • The first record is the parent document with level = 1.
    • The rest of records are children documents embedded in the parent doc, so I need the full path+filename from the parent of doc.
    • All records have same group ID.
    create table filetable (
    FileID varchar(20),
    GroupID varchar(10),
    Level varchar(1),
    Path varchar(255),
    Filename varchar(255),
    ExpectingResultofPath varchar(max)
    );

    insert into filetable (FileID, GroupID, Level, Path, Filename, ExpectingResultofPath)
    values
    ('D01','D01','1','\\server1','picture.png','\\server1'),
    ('D01.01','D01','2','','start.png','\\server1\picture.png'),
    ('D01.01.01','D01','3','','stop.png','\\server1\picture.png\start.png'),
    ('D01.02','D01','2','','read.doc','\\server1\picture.png');





  • Mr. Brian Gale

    SSC-Insane

    Points: 23075

    How does this look:

    CREATE TABLE [#filetable]
    (
    [FileID] VARCHAR(20)
    , [GroupID] VARCHAR(10)
    , [Level] VARCHAR(1)
    , [Path] VARCHAR(255)
    , [Filename] VARCHAR(255)
    , [ExpectingResultofPath] VARCHAR(MAX)
    );

    INSERT INTO [#filetable]
    (
    [FileID]
    , [GroupID]
    , [Level]
    , [Path]
    , [Filename]
    , [ExpectingResultofPath]
    )
    VALUES
    (
    'D01'
    , 'D01'
    , '1'
    , '\\server1'
    , 'picture.png'
    , '\\server1'
    )
    , (
    'D01.01'
    , 'D01'
    , '2'
    , ''
    , 'start.png'
    , '\\server1\picture.png'
    )
    , (
    'D01.01.01'
    , 'D01'
    , '3'
    , ''
    , 'stop.png'
    , '\\server1\picture.png\start.png'
    )
    , (
    'D01.02'
    , 'D01'
    , '2'
    , ''
    , 'read.doc'
    , '\\server1\picture.png'
    );

    WITH [cte]
    AS
    (
    SELECT
    [FileID]
    , [GroupID]
    , [Level]
    , [Path]
    , [Filename]
    , CAST([Path] AS VARCHAR(MAX)) AS [ExpectingResultofPath]
    , 1 AS [depth]
    FROM [#filetable]
    WHERE [Level] = 1
    UNION ALL
    SELECT
    [#filetable].[FileID]
    , [#filetable].[GroupID]
    , [#filetable].[Level]
    , [#filetable].[Path]
    , [#filetable].[Filename]
    , CAST([cte].[ExpectingResultofPath] + '\' + [cte].[Filename] AS VARCHAR(MAX))
    , [cte].[depth] + 1
    FROM [#filetable]
    JOIN [cte]
    ON [cte].[depth] = [cte].[Level]
    AND [cte].[FileID] = LEFT([#filetable].[FileID], LEN([#filetable].[FileID]) - 3)
    WHERE [#filetable].[Level] > 1
    )
    SELECT
    [cte].[FileID]
    , [cte].[GroupID]
    , [cte].[Level]
    , [cte].[Path]
    , [cte].[Filename]
    , [cte].[ExpectingResultofPath]
    FROM [cte]
    WHERE [cte].[Level] = [cte].[depth]
    ORDER BY FileID;

    DROP TABLE [#filetable];

     

    get to have some fun with recursive CTE's with this method.  Not going to be the most efficient approach, but should get you the results you are looking for.  If you were on SQL 2016, than LAG could be your friend, but on 2012 this is a bit more challenging.

  • ScottPletcher

    SSC Guru

    Points: 98492

    I thought LEAD / LAG were added in SQL 2012?!, although I'm not sure how useful they'd be here anyway.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • shogunSQL

    SSChasing Mays

    Points: 643

    Thank you very much Mr. Brian Gale.

    Wow. This is a very nice CTE works with Depth to find relationships from previous record with current record.

    LAG() sounds good. Would give it a try when I am on 2016. Thank you again.

  • Mr. Brian Gale

    SSC-Insane

    Points: 23075

    ScottPletcher is right - LEAD and LAG were introduced in 2012.  I am bad at this recently.  I thought they were a 2016 and newer feature.  Good to know!

    I think ScottPletcher is right too about LAG not being helpful here too.  I was just testing some stuff out with LAG and I can make it work great to get depth of 2, but any further than 2 and it gets stuck.  With my testing on this, I think we end up with the same recursive CTE approach when trying to use LAG.

    The more I think about it, the more I think that LAG won't work for this as you have a many-to-1 relationship from the child to parent (ie one parent group/level pair can have multiple children), so looking at the previous row isn't too helpful.

    A different approach than the recursive CTE though would be to use a loop.  Loops are inefficient in SQL, but they are an option.  A 3rd approach would be to do this at the application layer.  Advantage to doing it at the application layer is you can then thread it across different groupID's which may offer a performance boost (depending on the system it is running on).

    Now, which approach you go with for me would be based on "which approach can I support".  In the event any of these approaches has a bug in it (some use case not covered in the sample data that isn't captured by coincidence), it will be on you to fix it (with potential help on forums like this one), but trying to rely on a community forum for support DOES mean that you may be sitting and waiting for hours or days with it being broken.

  • sgmunson

    SSC Guru

    Points: 110508

    Yes, LEAD and LAG were indeed introduced in SQL 2012.   And I agree with Brian here regarding "which approach can I support?".   That's got to be the number one consideration.   Just taking code from the internet isn't the greatest idea if you don't fully and completely understand exactly what it does...

     

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply