展平包含引用SQL Server 2005中其他行的行的表(Flattening a table that contains rows that reference other rows in SQL

展平包含引用SQL Server 2005中其他行的行的表(Flattening a table that contains rows that reference other rows in SQL Server 2005)

我正面临一个问题，当你处理不完全规范化的表时，偶尔会出现这个问题。这是问题所在。想象一下有4列的表，让我们称这个表为dbo.Hierarchical。这是表的定义：

if OBJECT_ID('dbo.Hierarchical') is not null drop table dbo.Hierarchical create table dbo.Hierarchical ( colID int not null identity(1,1) primary key ,GroupName varchar(5) not null ,IsAtomic bit not null ,Constituent varchar(5) null )

此表可以具有Atomic的GroupName，这意味着它没有组件，或者不能是Atomic。在这种情况下，GroupName可以包含其他GroupNames。

为了清楚起见，让我们在表格中填写一些数据。

set nocount on insert into dbo.Hierarchical values ('A',0,'B') insert into dbo.Hierarchical values ('A',0,'C') insert into dbo.Hierarchical values ('B',1,'B') insert into dbo.Hierarchical values ('C',0,'K') insert into dbo.Hierarchical values ('C',0,'L') insert into dbo.Hierarchical values ('D',0,'E') insert into dbo.Hierarchical values ('D',0,'F') insert into dbo.Hierarchical values ('D',0,'G') insert into dbo.Hierarchical values ('E',1,'E') insert into dbo.Hierarchical values ('F',1,'F') insert into dbo.Hierarchical values ('G',0,'H') insert into dbo.Hierarchical values ('G',0,'I') insert into dbo.Hierarchical values ('H',1,'H') insert into dbo.Hierarchical values ('I',1,'I') insert into dbo.Hierarchical values ('J',1,'J') insert into dbo.Hierarchical values ('K',1,'K') insert into dbo.Hierarchical values ('L',1,'L') insert into dbo.Hierarchical values ('M',1,'M') insert into dbo.Hierarchical values ('N',1,'N') set nocount off

现在，如果我们从dbo.Hierarchical看一个简单的select *，我们得到以下结果：

GroupName colID IsAtomic Constituent A 1 0 B A 2 0 C B 3 1 B C 4 0 K C 5 0 L D 6 0 E D 7 0 F D 8 0 G E 9 1 E F 10 1 F G 11 0 H G 12 0 I H 13 1 H I 14 1 I J 15 1 J K 16 1 K L 17 1 L M 18 1 M N 19 1 N

哇，那是漫长的啰嗦。现在，注意前两行有GroupName A和Constiuents B和C. B是Atomic，所以它没有其他的constiuents。然而，C具有K，L（K和L是原子）。如何创建一个将该表展平的视图，以便我只看到GroupName和Atomic constiuents。在GroupName A的情况下，我应该看到3行

A B A K A L

I am facing a problem that occasionally comes up when you deal with not fully normalized table. Here is the problem. Imagine a table with 4 columns, and let's call this table dbo.Hierarchical. Here is the definition of the table:

This table can have a GroupName that is Atomic, which means that it doesn not have a component, or can not be Atomic. In this case, a GroupName can contain other GroupNames.

Lets fill the table with some data for clarity.

Now if we look at a simple select * from dbo.Hierarchical we get the following:

GroupName colID IsAtomic Constituent A 1 0 B A 2 0 C B 3 1 B C 4 0 K C 5 0 L D 6 0 E D 7 0 F D 8 0 G E 9 1 E F 10 1 F G 11 0 H G 12 0 I H 13 1 H I 14 1 I J 15 1 J K 16 1 K L 17 1 L M 18 1 M N 19 1 N

Whew, that was long winded. Now, notice that the first two rows have GroupName A and Constiuents B and C. B is Atomic, so it has no further constiuents. C, however, has constiuents K, L (K and L are Atomic). How can I create a view that will flatten this table out so that I only see GroupName and the Atomic constiuents. In the case of GroupName A, I shoud see 3 rows

A B A K A L

最满意答案

尝试一下：

--just a repeat of OP's original table and data DECLARE @Hierarchical table ( colID int not null identity(1,1) primary key ,GroupName varchar(5) not null ,IsAtomic bit not null ,Constituent varchar(5) null) set nocount on insert into @Hierarchical values ('A',0,'B');insert into @Hierarchical values ('A',0,'C'); insert into @Hierarchical values ('B',1,'B');insert into @Hierarchical values ('C',0,'K'); insert into @Hierarchical values ('C',0,'L');insert into @Hierarchical values ('D',0,'E'); insert into @Hierarchical values ('D',0,'F');insert into @Hierarchical values ('D',0,'G'); insert into @Hierarchical values ('E',1,'E');insert into @Hierarchical values ('F',1,'F'); insert into @Hierarchical values ('G',0,'H');insert into @Hierarchical values ('G',0,'I'); insert into @Hierarchical values ('H',1,'H');insert into @Hierarchical values ('I',1,'I'); insert into @Hierarchical values ('J',1,'J');insert into @Hierarchical values ('K',1,'K'); insert into @Hierarchical values ('L',1,'L');insert into @Hierarchical values ('M',1,'M'); insert into @Hierarchical values ('N',1,'N');set nocount off --declare and set starting position DECLARE @Start varchar(5) SET @Start='A' --get the data ;WITH HierarchicalTree AS ( SELECT GroupName, Constituent, 1 AS LevelOf FROM @Hierarchical WHERE GroupName=@Start UNION ALL SELECT t.GroupName, h.Constituent, t.LevelOf+1 FROM HierarchicalTree t INNER JOIN @Hierarchical h ON t.Constituent=h.GroupName WHERE h.Constituent!=h.GroupName AND h.IsAtomic=0 ) SELECT t.GroupName,t.Constituent FROM HierarchicalTree t INNER JOIN @Hierarchical h ON t.Constituent=h.GroupName WHERE h.IsAtomic=1

OUTPUT：

GroupName Constituent --------- ----------- A B A K A L (3 row(s) affected)

give this a try:

OUTPUT:

GroupName Constituent --------- ----------- A B A K A L (3 row(s) affected)

更多推荐