Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  Dealing with "chains" of data

Author  Topic  lvirden Starting Member 4 Posts Posted - 2012-02-14 : 10:59:41 I am trying to figure out how best to perform a query which draws data from two different tables, and in one of the tables, related data is in different rows. Here's my attempt at a really dumb example: There are 3 columns of interest * Logical.name from table cir * Related.cis from table cir (note this column contains one or mroe logical.name values, separated by control-M. These items are related in some way with the logical.name of the row.) * Subtype from table dev The report that I need should display Logical.name values, grouped by subtype, chaining through the related.cis field For example, if the tables look liked this Table: Cirelationship Logical.name related.cis A B E G B C C I D E F H F H G I H I J I K J K Table: device Logical.name Subtype A Biz 1 B C D Biz 2 E F G H I J K L Then the desired report might be: Biz 1 A B C E F G H I J K Biz 2 D Where the ordering in the report is not essential Notice that if the output includes Biz 3 with nothing under it, that is fine. The main importance is that all logical.name's related through the related.cis are displayed, no matter how nested. While in theory there should NOT be circular relationships, I don't think there is anything in the system to prevent them. Obviously, if a circle exists, we don't want the items to repeat over and over. Is this doable from a select statement (as opposed to having to write code to dynamically generate various select statements and then parsing the results)? Thank you!