Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

hierachy with miltiple parent Expand / Collapse
Author
Message
Posted Sunday, August 5, 2012 3:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:14 AM
Points: 105, Visits: 463
Childid Childname Parentid pid
100 Bingo 200 1
101 Pingo 201 1
102 Zingo 201 1
100 Bingo 201 2
101 Pingo 200 2
102 Zingo 201 2
100 Bingo 201 3
101 Pingo 201 3
102 Zingo 200 3
100 Bingo 200 4
101 Pingo 201 4
102 Zingo 200 4





As we can see in this table that ‘100’ has multiple parents for different Pids.
100=200 and 100=201 , So I want to creating a query that will check in previous id whether childid is mapped with new parentid . if parent has changed then create a new childid with same childname.
What about doing same thing in a historical table where the parent of 100 has changed many times.
How to get a data where we can find when all the parent of child has changed from all past Pid’s.
Post #1340253
Posted Monday, August 6, 2012 7:29 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:14 AM
Points: 3,618, Visits: 5,254
I think the reason no one has replied to you in 2 days is because you seem to have posted many questions, but none of them clearly enough for us poor readers (me at least) to understand.

Can you:
1. Provide DDL (table CREATE) statement.
2. Post your input data in a readily consumable format (e.g., SELECT/UNION ALL/SELECT) so that we don't need to do that.
3. Provide an example of what your expected results are.

With these things, you're more likely to get a tested, working solution.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1340966
Posted Tuesday, August 7, 2012 9:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:23 PM
Points: 13,272, Visits: 12,103
Something like this is what Dwain is talking about.

create table #Something
(
ChildID int,
ChildName varchar(20),
ParentID int,
PID int
)

insert #Something
select 100, 'Bingo', 200, 1 union all
select 101, 'Pingo', 201, 1 union all
select 102, 'Zingo', 201, 1 union all
select 100, 'Bingo', 201, 2 union all
select 101, 'Pingo', 200, 2 union all
select 102, 'Zingo', 201, 2 union all
select 100, 'Bingo', 201, 3 union all
select 101, 'Pingo', 201, 3 union all
select 102, 'Zingo', 200, 3 union all
select 100, 'Bingo', 200, 4 union all
select 101, 'Pingo', 201, 4 union all
select 102, 'Zingo', 200, 4

select * from #Something

drop table #Something

OK so we have a table. However I don't get the relationship here. None of these rows have a ParentID that exists. What is PID? Your explanation of what you want is incredibly unclear.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1341341
Posted Tuesday, August 7, 2012 9:48 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Does PID somehow translated to "historical" versus "present" in your question?

- 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
Post #1341367
Posted Tuesday, August 7, 2012 7:16 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:14 AM
Points: 3,618, Visits: 5,254
I've reread the questions now 3 times and still can't figure them out without some expected output.

Nice of you to post DDL though Sean.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1341645
Posted Wednesday, August 8, 2012 7:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:23 PM
Points: 13,272, Visits: 12,103

Nice of you to post DDL though Sean.


Thanks. Now if we can just the OP to clarify the question we can knock this out in about 3-4 minutes.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1341880
Posted Wednesday, August 8, 2012 12:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:14 AM
Points: 105, Visits: 463
create table #abc(insertid int,cityid int,parentcityid int,cityname varchar(50),parentname varchar(50))
insert into #abc
select 1,10,11,'A','B'
union
select 1,11,12,'B','C'
union
select 1,12,13,'C','D'
union
select 2,10,11,'A','B'
union
select 2,11,13,'B','D'
union
select 2,12,11,'C','A'

Output:
Insertid Cityid Parentid cityname parentname
1 10 11 A B
1 11 12 B C
1 12 13 C D
2 10 11 A B
2 11 13 B D
2 12 11 C A


Where ever there is a change in parent for same child for different insertid then there should be a new id assign to old id. Eg: in table for insertid 1 cityid 11 had parent c(id=12) but for insertid 2 cityid 11 has parented as13 . So the desired output should be like:

Insertid Cityid Parentid cityname parentname
1 10 11 A B
1 11 12 B C
1 12 13 C D
2 10 11 A B
2 14 13 B D
2 15 11 C A

I think now the post will be clear.
Post #1342143
Posted Wednesday, August 8, 2012 1:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:23 PM
Points: 13,272, Visits: 12,103
scottichrosaviakosmos (8/8/2012)
create table #abc(insertid int,cityid int,parentcityid int,cityname varchar(50),parentname varchar(50))
insert into #abc
select 1,10,11,'A','B'
union
select 1,11,12,'B','C'
union
select 1,12,13,'C','D'
union
select 2,10,11,'A','B'
union
select 2,11,13,'B','D'
union
select 2,12,11,'C','A'

Output:
Insertid Cityid Parentid cityname parentname
1 10 11 A B
1 11 12 B C
1 12 13 C D
2 10 11 A B
2 11 13 B D
2 12 11 C A


Where ever there is a change in parent for same child for different insertid then there should be a new id assign to old id. Eg: in table for insertid 1 cityid 11 had parent c(id=12) but for insertid 2 cityid 11 has parented as13 . So the desired output should be like:

Insertid Cityid Parentid cityname parentname
1 10 11 A B
1 11 12 B C
1 12 13 C D
2 10 11 A B
2 14 13 B D
2 15 11 C A

I think now the post will be clear.


We now have a clean table of sample data. Your desired output looks exactly like select * from #abc so I assume that is what that is.

However, you tried to explain what you want and it doesn't make any sense.


Where ever there is a change in parent for same child for different insertid then there should be a new id assign to old id. Eg: in table for insertid 1 cityid 11 had parent c(id=12) but for insertid 2 cityid 11 has parented as13 .


What does that mean? And what do you want? Do you want a trigger on this table that an update based on some rules that are still unclear (at least to me)? What is cityid? And what is the rule here? Does this table even have a primary key?


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1342151
Posted Wednesday, August 8, 2012 7:24 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:14 AM
Points: 3,618, Visits: 5,254
Hmmm... Strange requirement indeed. If I'm understanding this correctly, a quirky update might do it.

Set up your table like this:

create table #abc
(insertid int, cityid int, parentcityid int, cityname varchar(50), parentname varchar(50)
,PRIMARY KEY CLUSTERED (insertid, cityid, parentcityid))
insert into #abc
select 1,10,11,'A','B'
union all select 1,11,12,'B','C'
union all select 1,12,13,'C','D'
union all select 2,10,11,'A','B'
union all select 2,11,13,'B','D'
union all select 2,12,11,'C','A'


Then try this, but test it thoroughly against deeper cases to be sure that it works. It seems to for this limited test data anyway.

DECLARE @NextParentID INT = 0, @CityID INT = 0
UPDATE a
SET @NextParentID = CASE WHEN parentcityid >= @NextParentID THEN parentcityid + 1
WHEN CityID >= @NextParentID THEN CityID + 1
ELSE @NextParentID END
,@CityID = CityID = CASE WHEN (
SELECT CityID
FROM #abc b
WHERE b.InsertID = a.InsertID - 1 AND b.cityid = a.cityid AND
b.parentcityid <> a.parentcityid) IS NULL
THEN CityID ELSE CASE WHEN @CityID >= @NextParentID THEN @CityID + 1 ELSE @NextParentID END END
FROM #abc a
OPTION (MAXDOP 1)

SELECT * FROM #abc

DROP TABLE #abc





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1342331
Posted Friday, August 10, 2012 11:51 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:14 AM
Points: 105, Visits: 463
For those of you who have not understood my question. In one line , I have to do a data migration and have to find the child who have multiple parents. So wherever you have multiple parents then assign a new id to the child.
eg;
childid childname parentid
100 abc 200
100 abc 300

desired output

childid childname parentid
100 abc 200
101 abc 300


I hope now this will be clear. And I am doing this for historical data so query ll be quite complicated.
Post #1343750
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse