January 5, 2011 at 11:47 am
Hii,
Please check the below, I pasted my requirement in the form of an image.
'ID_Current' is just an auto increment column but 'ID_parent' depends upon the parent node ID value.
CURRENT TABLE
IDID_ParentID_CurrentTitleID_Note
1 Apple1
2 Ball1.1
3 Berry1.1.1
4 Black1.1.1.1
5 Htc1.1.1.1.1
6 Evo1.1.1.1.1.1
7 air1.1.1.1.1.2
8 3G1.1.1.1.2
9 4G1.1.1.1.2.1
10 Android1.1.1.1.2.2
I need to write a script to fill ID_Parent and ID_Current as below
IDID_ParentID_CurrentTitleID_Note
10 1 Apple1
21 2 Ball1.1
32 3 Berry1.1.1
43 4 Black1.1.1.1
54 5 Htc1.1.1.1.1
65 6 Evo1.1.1.1.1.1
75 7 air1.1.1.1.1.2
84 8 3G1.1.1.1.2
98 9 4G1.1.1.1.2.1
108 10 Android1.1.1.1.2.2
In this way, I need to fill 5000 rows. So I need to implement the script to do this task
Can anyone help with code direction for implement the script to generate 'ID_parent' based on the 'ID_Node' and 'ID_Current'
Thankyou
Naru
January 5, 2011 at 11:52 am
I can't see the image. Can you post code for the table?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 5, 2011 at 12:16 pm
Hi,
Added the tables and their data to understand the issue better.
Thankyou for you support
January 5, 2011 at 12:21 pm
I don't about anybody else but that doesn't really explain what you are trying to do. What makes one of those records a child of another? Is there some sort of cryptic logic in the 1.1.1.1.1.2.1.1.2 stuff that I am missing?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 5, 2011 at 12:39 pm
There is no crypted logic here
I have to generate parent_ID based on the the value we have in ID_Node (something like Tree)
For example :
The 1st value for ID_Node is 1 and Value is 'Apple'. So I will assume ID_Parent as 0 and ID_Current as 1
now 2nd value ID_Node is 1.1 and Value is 'Ball'. So I will set ID_Parent as 1 and ID_Current as 2( 'coz 1.1 already has the parent value 1 )
now 5th value ID_Node is 1.1.1.1 and value is Htc'.So I will set ID_Parent as 4 and ID_Current as 5
When you look at 1.1.1.1.1.1 & 1.1.1.1.1.2 i.e. ID_Node 6th and 7th
I am assuming the portion before .1 and .2 as parent, which is 5 so the ID_Parent for 6th and 7th will be 5 and ID_current will just increament by 1
so now while coding If I want to get the full tree for 'Air', whose ID_parent = 5 and ID_current = 7
I will know the tree is
Apple -> Ball-> Berry Black -> HTC-> AIR
and for 'Evo' it will be
Apple -> Ball-> Berry Black -> HTC-> Evo
January 5, 2011 at 1:19 pm
So I have to write a script for filling ID_parent field, for ID_Current it is just the next value….
For ID_Parent,
I need to take id_note value trim last two characters and when I get the value search for it in id_note filed and find its corresponding id and insert that vaule in id_parent.
This is what I want to do
January 5, 2011 at 2:06 pm
Something like this should do it. Notice how i included the create table and insert scripts.
create table NodeSample
(
ID int identity not null,
ID_Parent int null,
Title varchar(20) null,
ID_Note varchar(20) null
)
go
insert NodeSample (Title, ID_Note) select 'Apple', '1'
insert NodeSample (Title, ID_Note) select 'Ball', '1.1'
insert NodeSample (Title, ID_Note) select 'Berry', '1.1.1'
insert NodeSample (Title, ID_Note) select 'Black', '1.1.1.1'
insert NodeSample (Title, ID_Note) select 'Htc', '1.1.1.1.1'
insert NodeSample (Title, ID_Note) select 'Evo', '1.1.1.1.1.1'
insert NodeSample (Title, ID_Note) select 'air', '1.1.1.1.1.2'
insert NodeSample (Title, ID_Note) select '3G', '1.1.1.1.2'
insert NodeSample (Title, ID_Note) select '4G', '1.1.1.1.2.1'
insert NodeSample (Title, ID_Note) select 'Android', '1.1.1.1.2.2'
go
update ns
set ID_Parent = ns1.ID
from NodeSample ns
left join NodeSample ns1 on ns1.ID_Note = SUBSTRING(ns.ID_Note, 0, datalength(ns.ID_Note) -1)
where DATALENGTH(ns.ID_Note) > 2
update NodeSample set ID_Parent = 0 where ID_Parent is null
select * from NodeSample
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 5, 2011 at 2:34 pm
Hi,
Thankyou for the beautiful(easy to understand) script.
But this scirpt will fail to create the ParentID when ID_Note has something like this "1.1.1.4.2.10", " 1.1.1.4.2.11"
How should I handle this scenario
Thankyou
Naru
January 5, 2011 at 2:39 pm
instead of "datalength(ns.ID_Note) -1" you should take a look at the reverse and charindex functions. Basically you can reverse your string and get the index of the first ".". The remaining string (once reversed again) should work. Hope that gets you in the right direction.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 5, 2011 at 2:45 pm
Yes . It worked.
Thankyou Thankyou for the sweet and smile script.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply