Hierarchical order by TSQL
-
Dear Sir
create table Hierarchy
(
ParentID int,
ChildID int
)
go
insert into Hierarchy values (101,1),(1,6),(101,2),(2,4),(2,5),(5,6),(5,7),(101,3),(3,5)
Please refer the Hierarchical order Diagram in order to understand how this hierarchy order constructed.
we are focusing on filtering the parent ID.
when we pass the parameter 101 , output should display as follows from the Hierarchy table.
101/1/6
101/2/4
101/2/5/6
101/2/5/7
101/3/5/6
101/3/5/7
For 1 sql server output as follows
1/6
For 2 sql server output as follows
2/4
2/5/6
2/5/7
For 3 sql server output as follows
3/5/6
3/5/7
For 5 SQL Server output as follows
5/6
5/7
-
Hi and welcome to the forums. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
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/ -
Hi, Welcome to the forums.
I'll be glad to help you, but it would take me a while to prepare the data to be able to test any solution. Could you post your sample data as DDL and insert statements (in text not images)?
Meanwhile, I'll try to get a generic solution for you.
-
I have updated my post . please refer the comments above.
Many thanks for your contribution and support towards to me .
-
hifaizal90 (2/5/2014)
I have updated my post . please refer the comments above.Many thanks for your contribution and support towards to me .
Thanks for the updated info. The problem here is that you have what sort of appears to be an adjacency list but your data is not normalized.
Typically in these situations you have a row that has an ID. Then you have a ParentID column. You seem to have a parentid column and a childid column. You have nothing in your table that can function as a primary key.
Can you explain what ChildID is?
I think part of the issue here is that you have obfuscated the real information so much that it is nearly unusable.
_______________________________________________________________
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/ -
I might not be realizing the problem that Sean is talking about, but here's an example on how to do it. Be sure to understand what's going on before using it. And test it over a large amount of records as you might find that there can be performance problems.
DECLARE @Value int = 5;
WITH rCTE AS(
SELECT CAST( CAST( ParentID AS varchar(10))+ '/' + CAST( ChildID AS varchar(10)) AS varchar(8000)) AS String,
ParentID,
ChildID
FROM Hierarchy
WHERE ParentID = @Value
UNION ALL
SELECT CAST( r.String + '/' + CAST( h.ChildID AS varchar(10)) AS varchar(8000)) As String,
h.ParentID,
h.ChildID
FROM Hierarchy h
JOIN rCTE r ON h.ParentID = r.ChildID
)
SELECT String
FROM rCTE r
WHERE NOT EXISTS( SELECT *
FROM rCTE x
WHERE x.String LIKE r.String + '%' --Contains the string
AND x.String > r.String) --And is larger than the string
ORDER BY String
-
Luis Cazares (2/5/2014)
I might not be realizing the problem that Sean is talking about...Or maybe my brain is making it harder than it really is. 🙂
_______________________________________________________________
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/ -
Dear Experts
@luis Cazares\@Sean Lange: Thanks for your effort and indeed this is great piece of work ,Knowledge Sharing and outstanding support.
@luis Cazares: This is an excellent script ..
As I can see first getting the relationship
SELECT CAST( CAST( ParentID AS varchar(10))+ '/' + CAST( ChildID AS varchar(10)) AS varchar(8000)) AS String,
ParentID,
ChildID
FROM Hierarchy
WHERE ParentID = @Value
Secondly It look for cross join with CTE child and main table sub class parent ...Based on this it keeps getting Main table child and the required output we are getting that is fantastic.
I have been told to extend this query based on the below information.
Could you please teach me Sir.
No parameters passing: when we select * from rcte
Please refer the attached file as if it's not clear.
The output should display as follow
P Child Output
101 1 101/1/6
1 6 1/6
101 2 101/2/4
2 4 2/4
2 5 2/5/6
5 6 5/6
5 7 5/7
101 3 101/3/5
3 5 3/5
-
Just run the same query but comment out the where clause in the base part of the rCTE.
WITH rCTE AS(
SELECT CAST( CAST( ParentID AS varchar(10))+ '/' + CAST( ChildID AS varchar(10)) AS varchar(8000)) AS String,
ParentID,
ChildID
FROM Hierarchy
--WHERE ParentID = @Value
UNION ALL
SELECT CAST( r.String + '/' + CAST( h.ChildID AS varchar(10)) AS varchar(8000)) As String,
h.ParentID,
h.ChildID
FROM Hierarchy h
JOIN rCTE r ON h.ParentID = r.ChildID
)
SELECT String
FROM rCTE r
WHERE NOT EXISTS( SELECT *
FROM rCTE x
WHERE x.String LIKE r.String + '%' --Contains the string
AND x.String > r.String) --And is larger than the string
ORDER BY String
_______________________________________________________________
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/ -
Dear Experts
I don't think we can use the same query.
In the first row we don't want output 101/1 for parentID=101,childID=1
Instead the output should be 101/1/6 in the first row since 1 has the branch 6.
Second row 1/6 remains the same since 6 doesn't have any branch
In the third row we want the output to be 101/2/4 not 101/2.
In the fourth row output 2/4 remains the same since 4 don't have any branch.
So it is filtering the Parent ID on row by row basis.
Please refer the output as follows and view the attached images for further information.
select * from CTE it should appear as follows includes output column for all rows.
PC
1011
output : 101/1/6
PC
16
output :1/6
PC
1012
output :101/2/4
PC
24
output :2/4
PC
25
output :2/5/6
PC
56
output : 5/6
PC
57
output :5/7
PC
1013
output :101/3/5
PC
35
output :3/5
-
Dear Sir
The above query produces the required result even though 101 not appear in the parent Id since it is top level parent.
PIDCID
16
output :1/6
16
output : 101/1/6
Many Thanks Experts.
-
hifaizal90 (2/7/2014)
Dear SirThe above query produces the required result even though 101 not appear in the parent Id since it is top level parent.
Which one?
-
I think your table definition and table data are wrong. Or may be I don't know in what case use your case.
That's because in a normal situation that includes parent/child you must to have a primary key, and a parent key. (ColumnID, ParentColumnID)
Your data has 2 parents for child 5 and 6. And I don't see a parent child relation, but a many to many relation. In your data you are limited to a max of 3 step cascade, in a relation (Id,parentId) you have unlimited steps.
In the third output row, your result must to be 101/2/4-5, so I think your original approach is wrong.
Anyway you can get your desired results making a cursor and building the output, but you gonna have problems like the third row
regards
Martin
-
I wonder why people consider the data structure is wrong.
If they are trying to store the structure of a process flow, where a task can have multiple successors and predecessors, would that be wrong?
We have no idea of the functionality of this data, so without further knowledge it's impossible to say if it's wrong or right.
I've been thinking on the possible solution, but I can't figure one out.
It seems that you edited the problem. Could you explain again what would be the input, the output and the rules to get it?
-
It is not wrong to make a relationship of several predecessors and successors, but his logic contradicts the desired result, as with row 3 and 5 for example.
I'm just saying that if he wants to represent a parent child relationship is not the right way.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply