July 31, 2020 at 5:25 pm
Hi,
I have a table as you can see in codes below. Can you please help me with generating the last column 'ExpectingResultofPath'?
Considerations:
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');
July 31, 2020 at 9:18 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 31, 2020 at 10:31 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
July 31, 2020 at 10:40 pm
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.
August 4, 2020 at 4:04 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 6, 2020 at 2:54 pm
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 14, 2020 at 7:01 pm
Brian,
Thank you for your thoughtful respond. You're right about LAG won't work for this and I think I need a while loop for this because the level can be greater than 3 and it can't get from the previous record.
If we fetch a record that is being updated in a while loop, we would take (path + filename ) from the previous level (or parent record). Not from previous record.
Can you or anyone help me with the while loop? I understand it's bad in performance but it would not lock the table and interfere with other SQL transactions in queue. Thanks in advance.
August 14, 2020 at 7:57 pm
Does my sample code not handle depths of 4? I just tried it with a depth of 4 and it worked fine.
Do you have some sample data that is not giving you the expected output?
The recursive CTE should work for depths up to 50 (by default... can increase that... it is a limitation/feature of recursive CTE's to prevent infinite recursion).
I am not sure that a loop would be any faster than using a recursive CTE. BUT if a loop is the way you want to go, my approach to this would be to first update all of the rows where the depth = 1 because you know that value (it is stored in a previous column). Then into the loop - update each row where depth = 2 with the value from depth of 1 with the filename appended. Then repeat with depth = 3 and value from depth = 2 and repeat until depth of n.
Sample code showing both CTE and LOOP approaches:
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'
, ''
)
, (
'D01.01'
, 'D01'
, '2'
, ''
, 'start.png'
, ''
)
, (
'D01.01.01'
, 'D01'
, '3'
, ''
, 'stop.png'
, ''
)
, (
'D01.01.01.01'
, 'D01'
, '4'
, ''
, 'stop.png'
, ''
)
, (
'D01.02'
, 'D01'
, '2'
, ''
, 'read.doc'
, ''
)
, (
'D01.02.01'
, 'D01'
, '3'
, ''
, 'read.doc'
, ''
);
-- CTE METHOD
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[cte].[FileID];
-- LOOP METHOD
DECLARE @counter INT = 2;
DECLARE @maxLevel INT;
SELECT
@maxLevel = MAX([Level])
FROM[#filetable];
UPDATE
[#filetable]
SET
[ExpectingResultofPath] = [Path]
WHERE[Level] = 1;
WHILE (@counter <= @maxLevel)
BEGIN
UPDATE
[#filetable]
SET
[ExpectingResultofPath] = [newvalue]
FROM
(
SELECT
FileID
, [ExpectingResultofPath] + '\' + [Filename] AS [newvalue]
FROM[#filetable]
WHERE[Level] = (@counter - 1)
) AS [updated]
WHERE[Level] = @counter
AND [updated].[FileID] = LEFT([#filetable].[FileID], LEN([#filetable].[FileID]) - 3)
SELECT
@counter = @counter + 1;
END;
SELECT
[FileID]
, [GroupID]
, [Level]
, [Path]
, [Filename]
, [ExpectingResultofPath]
FROM[#filetable];
DROP TABLE [#filetable];
Both are giving the same result and I increased the data set to a depth of 4 without changing anything with the CTE method. I also blew out the "Expected result of path" values from the source data to prove that I am not reusing that data in the final result sets.
I think the big reason why LAG won't work for this (even with a loop) is that you have a 1:many relationship, what do you consider the "previous row" when you have 1:many relationship and how do you "lag" to from many back to the one? Much better to join where root level is when level = 1 and child levels are where level is greater than 1. Otherwise you will first need to break the 1:many relationship down to a 1:1 relationship and then you could use LAG. But LAG (to me) feels like the wrong tool for the job.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 14, 2020 at 9:33 pm
here is another sample. I can't get it to work.
create table #filetable (
FileID varchar(255),
GroupID varchar(255),
Level varchar(1),
Path varchar(255),
Filename varchar(255),
ExpectingResultofPath varchar(max)
);
insert into #filetable (FileID, GroupID, Level, Path, Filename, ExpectingResultofPath)
values
('ABCD-01','ABCD-01','1','\\server1','picture.png','\\server1'),
('ABCD-01.0001','ABCD-01','2','','start.png','\\server1\picture.png'),
('ABCD-01.0001.0001','ABCD-01','3','','stop.png','\\server1\picture.png\start.png'),
('ABCD-01.0002','ABCD-01','2','','read.doc','\\server1\picture.png'),
('ABCD-02','ABCD-02','1','\\server2','write.msg','\\server2'),
('ABCD-02.0001','ABCD-02','2','','work.doc','\\server2\write.msg'),
('ABCD-02.0001.0001','ABCD-02','3','','read2.doc','\\server2\write.msg\work.doc'),
('ABCD-02.0001.0001.0001','ABCD-02','4','','copy.doc','\\server2\write.msg\work.doc\read2.doc'),
('ABCD-02.0002','ABCD-02','2','','me.mp4','\\server2\write.msg')
;
select * from #filetable
--drop table #filetable
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;
August 14, 2020 at 9:41 pm
Please disregard my previous reply. I actually got it to work now with CTE.
create table #filetable (
FileID varchar(255),
GroupID varchar(255),
Level varchar(1),
Path varchar(255),
Filename varchar(255),
ExpectingResultofPath varchar(max)
);
insert into #filetable (FileID, GroupID, Level, Path, Filename, ExpectingResultofPath)
values
('ABCD-01','ABCD-01','1','\\server1','picture.png','\\server1'),
('ABCD-01.0001','ABCD-01','2','','start.png','\\server1\picture.png'),
('ABCD-01.0001.0001','ABCD-01','3','','stop.png','\\server1\picture.png\start.png'),
('ABCD-01.0002','ABCD-01','2','','read.doc','\\server1\picture.png'),
('ABCD-02','ABCD-02','1','\\server2','write.msg','\\server2'),
('ABCD-02.0001','ABCD-02','2','','work.doc','\\server2\write.msg'),
('ABCD-02.0001.0001','ABCD-02','3','','read2.doc','\\server2\write.msg\work.doc'),
('ABCD-02.0001.0001.0001','ABCD-02','4','','copy.doc','\\server2\write.msg\work.doc\read2.doc'),
('ABCD-02.0002','ABCD-02','2','','me.mp4','\\server2\write.msg')
;
WITH [cte]
AS
(
SELECT
[FileID]
, [GroupID]
, [Level]
, [Path]
, [Filename]
, CAST([Path] AS VARCHAR(MAX)) AS [NewResult_forPath]
, 1 AS [depth]
FROM[#filetable]
WHERE[Level] = 1
UNION ALL
SELECT
[#filetable].[FileID]
, [#filetable].[GroupID]
, [#filetable].[Level]
, [#filetable].[Path]
, [#filetable].[Filename]
, CAST([cte].[NewResult_forPath]+ '\' + [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]) - 5)
WHERE[#filetable].[Level] > 1
)
SELECT
[cte].[FileID]
, [cte].[GroupID]
, [cte].[Level]
, [cte].[Path]
, [cte].[Filename]
, [cte].[NewResult_forPath]
FROM[cte]
WHERE [cte].[Level] = [cte].[depth]
ORDER BY FileID;
drop table #filetable
August 14, 2020 at 9:46 pm
That is an easy fix - you need to change the JOIN condition in the recursive CTE to include a longer FileID. Your old one was 3 digits, your new one is 5. So in the JOIN, on the LEFT function, change the 3 to a 5.
The old one was comparing the file ID D01 to LEFT('D01.01',3) OR 'D01' and comparing D01.01 to LEFT('D01.01.01',6) OR 'D01.01'. See? We are stripping off the last set of digits so we can compare it to the previous row AND keep it in the same grouping.
With your new one, you are comparing ABCD-01 to LEFT('ABCD-01.0001',3) OR 'ABCD-01.0' which does not match, so you need to bump that 3 up to a 5 and then they will match.
Now, if that hard coded bit of logic won't work, then you get to have more fun as you need to find the location of the last . character. This is possible, but adds a bit more complexity to the query.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 14, 2020 at 9:57 pm
Yup, it made sense just now about the join clause you mentioned.
well for this example, we can hard-coded. but if we must not hard-code it, I would take off 5 last characters which is suffix with a period and padding with zeros and 4 digits.
Thank you so much for helping me today. You're awesome!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply