July 2, 2011 at 2:14 am
Hi,
Its been a while since i have done procedural SQL so would appreciate any help you can give to help get me back over the re-learning curve.
I have a table :
CREATE TABLE Hierarchy (
Node varchar(255)NOT NULL ),
ParentNode varchar(255),
Level tinyint,
Lineage varchar(255) )
Which contains data similar to the below to 10 levels
Node Parent Node Level Lineage
UK NULL 1 \UK
England UK 2 \UK\England
Scotland UK 2 \UK\Scotland
Wales UK 2 \UK\Wales
..
What I now need to do is replace the lineage with IDs based on the node and pad 10 chars long:
Node Parent Node Level Lineage
UK NULL 1 \0000000001
England UK 2 \0000000001\0000000002
Scotland UK 2 \ 0000000001\0000000003
Wales UK 2 \ 0000000001\0000000004
....
What would be the best\ quickest way to achieve this?
Thanks
July 2, 2011 at 11:52 am
Intrepid121 (7/2/2011)
....What would be the best\ quickest way to achieve this?
You could attack this using basic string replacement but I would not recommend it.
The proper way IMO is to do this in a single query that makes use two cascading CTEs and ignores the existence of the Lineage column. The first CTE will add row numbers using the ROW_NUMBER() function to the entire set. The second CTE will be recursive and will refer to the first CTE. The second CTE will walk the hierarchy using the Node and ParentNode columns and will build its own lineage column values using the padded row numbers from the first CTE.
I have worked the problem out on my side but I would like to see what you have done.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply