Hierarchical Query

Question

Hierarchical Query

mikefle

Mr or Mrs. 500

Points: 519
More actions
February 29, 2012 at 8:42 am

#73292

I am trying to write a query that will convert;
ParentChild
AB
BC
BD
Into;
ParentChild
AC
AD
Based on the following information;
NodeEnabled
AYes
BNo
CYes
DYes
i.e. where a node is not enabled, the relationship is inheritied from the parent
Tricky?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

It is very tricky without some ddl and sample data to work with. A clear explanation of what you want as output would help to. Take a look at the first link in my signature.

_______________________________________________________________

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/

mikefle Mr or Mrs. 500 Points: 519 More actions · Answer 2

Thanks Sean,

CREATE TABLE [dbo].[Tree](

[Parent] [nchar](10) NULL,

[Child] [nchar](10) NULL)

CREATE TABLE [dbo].[Status](

[Node] [nchar](10) NULL,

[Enabled] [nchar](10) NULL)

--Insert values

insert into Tree values('A','B')

insert into Tree values('B','C')

insert into Tree values('B','D')

insert into Status values ('A','Yes')

insert into Status values ('B','No')

insert into Status values ('C','Yes')

insert into Status values ('D','Yes')

Now I want to produce a query which will show relationships which take into account the disabled values. i.e. A is related directly to C and D, and display the results in a table containing a Parent and Child column.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

thanks for the ddl and sample data. What do you want as a result of your query based on this sample data? I am not getting what you mean taking the disabled status into account.

_______________________________________________________________

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/