All geek questions in one place

Smoothing a table containing rows that reference other rows in SQL Server 2005

I ran into a problem that sometimes occurs when you are dealing with a not fully normalized table. Here is the problem. Imagine a 4-column table and call this dbo.Hierarchical table. Here is the table definition:

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 may have the same name GroupName, which means that it does not have a component or cannot be Atomic. In this case, the GroupName may contain other group names.

Allows you to fill in 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.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

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

. , GroupName A Constiuents B C. B - Atomic, . C, , K, L (K L - ). , , GroupName Atomic. GroupName A,

A B
A K
A L
+3
3

:

--just a repeat of OP 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

:

GroupName Constituent
--------- -----------
A         B
A         K
A         L

(3 row(s) affected)
+4

, , , , . , CTE.

select a.GroupName,
        b.Constituent

From dbo.Hierarchical a

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

, , ?

0

sql script, . .

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

Source: https://habr.com/ru/post/1747215/

More articles:


All Articles