Create Sort order on temp table using CTE

Question

Post reply

Create Sort order on temp table using CTE

ian.merlino

Grasshopper

Points: 20
More actions
March 5, 2008 at 5:11 am

#187284

Hi guys,
I'm kinda battling with this one.
I've created a temp table and inserted some data. This table displays the each employee and their direct manager. What I need is the sortorder column to display a combination of the slot and all of the parent's so I can order the table by sortorder eg:
create table table2 (slot int, parent int, sortorder NVARCHAR(10))
slot parent sortorder
----------- ----------- ----------
10 20
20 30
25 30
27 30
15 27
slot parent sortorder
----------- ----------- ----------
10 20 2010
20 30 3020
25 30 3025
27 30 3027
15 27 302715 <------- 3rd Level.
I can get my code to do this for 2 levels down and when it come to the 3rd level I get the result 2715 instead of 302715.
Any help on this would be greatly appreciated. thanks.

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

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

ian.merlino Grasshopper Points: 20 More actions · Answer 1

Done.

I've created a cursor and then did the following, works fine.

SET @tempSlotID = @vintSlotID

SET @tempSortOrder = ''

WHILE (@tempSlotID IN (SELECT SlotID FROM #ReportingTree))

BEGIN

SET @tempSortOrder = CAST(@tempSlotID AS NVARCHAR(60)) + @tempSortOrder

SET @tempID = (SELECT ParentSlotID

FROM #ReportingTree

WHERE SlotID = @tempSlotID)

SET @tempSlotID = @tempID

END

UPDATE ReportTree

SET ReportTree.SortOrder = @tempSortOrder

FROM #ReportingTree ReportTree

WHERE ReportTree.SlotID = @vintSlotID

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

Ok... thanks for the feedback...

--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)