Flattening a table that contains rows that reference other rows in SQL Server 2005

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:

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
)

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.

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.Hierarc开发者_如何学JAVAhical 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

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

give this a try:

--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)

Well this does what you have asked for but it will only work if it's nested once. If you need recursion then you would have to use a CTE.

select a.GroupName,
        b.Constituent

From dbo.Hierarchical a

Left Join dbo.Hierarchical b on a.Constituent = b.GroupName

Is this what you need or have I missed the point completely?

For the sake of completeness, I've attached the entire sql script file that setups up the problem and shows the solution. Again, Hattip to KM.

 use tempdb
go

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
)

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

--  see what the over nomalized table looks like 
--  before you call the CTE. Notice how A has
--  Constiuents B, and C. And further down
--  C is made up of K, and L.

--  select * from dbo.Hierarchical

go


--  Use the CTE to 
;WITH HierarchicalTree AS 
( 
    SELECT  
        GroupName, Constituent,  1 AS LevelOf 
        FROM dbo.Hierarchical
        --WHERE GroupName=@Start 
    UNION ALL 
        SELECT  
            t.GroupName, h.Constituent, t.LevelOf+1 
        FROM HierarchicalTree         t 
            INNER JOIN dbo.Hierarchical  h ON t.Constituent=h.GroupName 
        WHERE h.Constituent!=h.GroupName AND h.IsAtomic=0 
) 


--  Now, notice this query will give us A with the it's 
--  Constiuent elements B, K, and L
SELECT 
    t.GroupName,t.Constituent, h.IsAtomic, t.LevelOf
    FROM HierarchicalTree        t 
        INNER JOIN  dbo.Hierarchical h  ON  t.Constituent=h.GroupName 
    --WHERE h.IsAtomic=1 
    Where h.Constituent = h.GroupName
order by 
    t.GroupName

if  OBJECT_ID('tempdb..Hierarchical') is not null
    drop table  dbo.Hierarchical