October 18, 2010 at 8:48 am
Hi all,
I have this table:
create table #t1 (item int not null, pre int null, post int null)
insert into #t1 values ( 4, 3, null)
insert into #t1 values ( 3, 2, 4)
insert into #t1 values ( 2, 1, 3)
insert into #t1 values ( 1, null, 2)
select * from #t1
--how can I transform that table into the following two tables? In real life #t1 has round about 2 millions of rows
--and 200,000 different items
create table #t2 (item int not null, pre int null)
insert into #t2 values ( 4, 3 )
insert into #t2 values ( 4, 2 )
insert into #t2 values ( 4, 1 )
insert into #t2 values ( 3, 2 )
insert into #t2 values ( 3, 1 )
insert into #t2 values ( 2, 1 )
insert into #t2 values ( 1, null )
select * from #t2
create table #t3 (item int not null, post int null)
insert into #t3 values ( 4, null )
insert into #t3 values ( 3, 4 )
insert into #t3 values ( 2, 3 )
insert into #t3 values ( 2, 4 )
insert into #t3 values ( 1, 2 )
insert into #t3 values ( 1, 3 )
insert into #t3 values ( 1, 4 )
select * from #t3
October 18, 2010 at 9:00 am
So, assuming I understand you correctly, #t1 is already populated and you just want to split the data into 2 different tables?
SELECT ID, pre
INTO #t2
FROM #t1
SELECT ID, post
INTO #t3
FROM #t1
Something tells me that isn't really what you are after though... perhaps you can further describe your scenario so we can provide a better answer?
October 18, 2010 at 10:41 am
What's the purpose of the split? Can one "pre" have more than one "post" for a given "item", or vice versa?
- 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
October 18, 2010 at 11:24 am
GSquared (10/18/2010)
What's the purpose of the split? Can one "pre" have more than one "post" for a given "item", or vice versa?
That's a very good question indeed. I was wondering if this is to fix some 1-to-n relationship that was not properly implemented or, changed overtime from 1-1 to 1-n
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 19, 2010 at 1:24 am
sorry getoffmyfoot,
SELECT item, pre
INTO #t2
FROM #t1
is not what I need. please look at the manually filled #t2. it's not just a split.
#t1 is a bloody old relationship-table on a mainframe. long time ago to save memory, the relationship was not installed 1:n but als follows:
item 4 has parent 3,
item 3 has parent 2,
item 2 has parent 1,
item 1 has no parent
it now has to be resolved to an 1:n Relationship as follows ,
item 4 has parent 3, 2, 1,
item 3 has parent 2, 1
item 2 has parent 1,
item 1 has no parent
depth can be up to 1,400 parents. I tried it with an recursive cursor loop. It took nearly two hours. that's too long.
Do You have any JOIN idea? Using temp-tables or so...
Regards
Ralf
October 19, 2010 at 8:46 am
Hi,
I don't sure that this will be faster but try this:
with RecursionCTE (item,Pre,parents)
as
(
select item,
Pre,
cast('',varchar(max)) as parents
from #t1
where Pre is null
union all
select R1.item,
R1.Pre,
cast(R2.parents+ '.' + cast(R1.Pre as varchar(10)) as varchar(max)) as parents
from #t1 as R1
join RecursionCTE as R2 on R1.pre = R2.item
)
select * from RecursionCTE
I didn't use the POST column but it can be easily add.
hope i helped a little
October 19, 2010 at 8:59 am
kupy,
many thank's! that's not exactly, what I want, but it helps.
Kindly Regards
Ralf
October 19, 2010 at 12:24 pm
Take a look at this article, and at the referenced material by Joe Celko. It will probably help you figure out how best to build the hierarchy in your database.
Article: http://www.sqlservercentral.com/articles/T-SQL/65540/
- 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
October 20, 2010 at 4:15 am
GSquared,
many thanks.
Ralf
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply