October 3, 2009 at 5:35 pm
Hi, new to the boards, been using SQL Server for about a month.
I will show an example of what I am attempting to do. I have a table with two columns, an ID field, and a pointer field. The pointer field is the ID field of another record within the same table. I will show a very easy example of this.
ID Pointer
1 2
2 3
3 4
4 null
5 6
6 7
What I am attempting to do is display two results. The ID field, and a concatenated result of the all the pointers from that ID until it hits a null entry. So for the table above, the result would look like the following:
ID Finished Path
1 2 + 3 + 4
2 3 + 4
3 4
4 null
5 6 + 7
6 7
So basically I need to update a variable for each row by moving through the table, then resetting that variable for each new row. I know how to do this with variables using a cursor, but I have been told cursors generally are a very bad idea. I keep hearing about "set" based approaches, but I am not familiar with what that concept means. I have never worked with stored procedures, but can learn about them if that is the needed solution. Can someone help me with how to manipulate the variables to get the desired result?
October 3, 2009 at 8:31 pm
Hierarchical code is so ugly in SQL Server. I dislike recursive CTE's even more than a Loop. So here's the loop version. Note that 5, 6, and 7 are "tree orphans". If you want to include them in their own tree, you need to add a 7,NULL record to the sample data.
--=======================================================================================
-- Setup some test data... note that nothing in this section is part of the actual
-- solution.
-- Jeff Moden
----=======================================================================================
--===== Setup a "quiet" environment
SET NOCOUNT ON
--===== Create a table to hold some test data.
-- This is NOT part of the solution
CREATE TABLE #yourtable
(
ID VARCHAR(10),
Pointer VARCHAR(10)
)
--===== Populate the test table with the "cyclic and other data
INSERT INTO #yourtable
(ID,Pointer)
SELECT '1','2' UNION ALL --All groups in the cyclic have double Pointers
SELECT '2','3' UNION ALL
SELECT '3','4' UNION ALL
SELECT '4',NULL UNION ALL
SELECT '5','6' UNION ALL
SELECT '6','7'
--=======================================================================================
-- The following code makes a Hierarchy "sister" table with strings that are used
-- to traverse various hierarchies.
--=======================================================================================
--===== Create and seed the "Hierarchy" table on the fly
SELECT ID,
Pointer,
Level = 0, --Top Level
HierarchyString = CAST(CAST(ID AS CHAR(10))+' ' AS VARCHAR(8000))
INTO #Hierarchy
FROM #yourtable
WHERE Pointer IS NULL
--===== Declare a local variable to keep track of the current level
DECLARE @Level INT
SET @Level = 0
--===== Create the hierarchy in the HierarchyString
WHILE @@ROWCOUNT > 0
BEGIN
SET @Level = @Level + 1
INSERT INTO #Hierarchy
(ID, Pointer, Level, HierarchyString)
SELECT y.ID,y.Pointer, @Level, CAST(y.ID AS CHAR(10))+' '+h.HierarchyString
FROM #yourtable y
INNER JOIN #Hierarchy h
ON y.Pointer = h.ID --Looks for Pointers only
AND h.Level = @Level - 1 --Looks for Pointers only
WHERE NOT EXISTS (SELECT 1 FROM #Hierarchy h1 WHERE h1.ID = y.ID AND h1.Pointer = y.Pointer)
--WHERE clause above keeps runaway cyclic groups from occuring
END
--=======================================================================================
-- Now, demo the use of the sister table
--=======================================================================================
--===== Display the entire tree
SELECT ID,
Pointer,
Level,
SUBSTRING(HierarchyString,12,LEN(HierarchyString))
FROM #Hierarchy
ORDER BY HierarchyString
--===== Display all the orphans
SELECT y.ID,y.Pointer
FROM #yourtable y
WHERE NOT EXISTS (SELECT 1
FROM #Hierarchy h1
WHERE h1.ID = y.ID
AND h1.Pointer = y.Pointer)
AND y.Pointer > '' --A bit faster than IS NOT NULL
DROP TABLE #yourtable, #Hierarchy
--Jeff Moden
October 3, 2009 at 8:34 pm
And to answer the title of this thread, no, I don't believe there's anyway to do this in SQL Server without some sort of loop whether it's a Cursor, While Loop, or (ugh!) Recursion (which is nothing more than a hidden loop).
--Jeff Moden
October 4, 2009 at 1:36 pm
Thank you for your help sir!
October 4, 2009 at 7:51 pm
huston.dunlap (10/3/2009)
...I know how to do this with variables using a cursor, ...
Actually, this is a more difficult problem than it at first appears, and there are some open questions here too. It would probably be easier to start from how you would do this with a cursor, and then we could better advise you how to proceed form there.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 4, 2009 at 8:03 pm
huston.dunlap (10/3/2009)
..I will show an example of what I am attempting to do. I have a table with two columns, an ID field, and a pointer field. The pointer field is the ID field of another record within the same table. I will show a very easy example of this.ID Pointer
1 2
2 3
3 4
4 null
5 6
6 7
What I am attempting to do is display two results. The ID field, and a concatenated result of the all the pointers from that ID until it hits a null entry. So for the table above, the result would look like the following:
ID Finished Path
1 2 + 3 + 4
2 3 + 4
3 4
4 null
5 6 + 7
6 7
Hmm, what do you do if say both 2 and 3 pointed to 4?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 4, 2009 at 8:10 pm
Hmm, somehow I missed that Jeff already answered this. Never mind ...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply