Iterating a variable through a table and then resetting it without using cursors

Question

Iterating a variable through a table and then resetting it without using cursors

huston.dunlap

SSCommitted

Points: 1551
More actions
October 3, 2009 at 5:35 pm

#215973

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?

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

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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 1

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

huston.dunlap SSCommitted Points: 1551 More actions · Answer 3

huston.dunlap

SSCommitted

Points: 1551

October 4, 2009 at 1:36 pm

#1061492

Thank you for your help sir!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

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]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

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]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

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]