• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 761
  • Last Modified:

How to create a self-referencing table view programmatically

I have a need to represent data hierarchically.  I read this article which was very helpful that showed how to use CTEs against a self-referencing table.  You can see in the article that the author manually created an employees self-referencing table with 8 records.  

http://www.theserverside.net/tt/articles/showarticle.tss?id=HeirarchicalQueries

I would like to attempt to do this with my data.  However, I don't know how to create a self-referencing table *programmatically*.  I wanted to create a view that showed data in this format.  However, my table has 31,000 rows in it and I am unsure how to create a view or snapshot that would create a self referencing table like this.   Can it be done in a view?  Or does it have to be in a refreshing snapshot of some sort?  How is it built?

Let's say I there's an org chart with company code ==> business unit ==> workgroup.  Records may look like this:

CCD        BU        WG
----------------------------------
123         ABC        A11
123         DEF         A24
456         DDD        B17
456         EEE         B18

I guess the self referencing table would look like:

ID    Object  Reference   Type
----------------------------------------
1      123     1    Company
2      456    2     Company
3       EEE   2     Business Unit
4      DDD   2     Business Unit
5      DEF    1    Business Unit
6      ABC   1   Business Unit
7      A11    6   Workgroup
8      A24    5   Workgroup


I don't know if this is right.  But if it is, I would I build that?   Please read the link above for an example of what I am trying to do.  The first step is to get that self-referencing table, and I am asking how to create a view for that or some information on how to construct it.  It's difficult to wrap my mind around.


0
dentyne
Asked:
dentyne
  • 3
  • 2
1 Solution
 
YveauCommented:
This will do ...

create table Y (ID int not null, Object varchar(10), Reference int, Type varchar(25))
alter table Y add constraint pkY primary key (ID)
alter table Y add constraint fkYY foreign key (Reference) references Y (ID)

Hope this helps ...
0
 
dentyneAuthor Commented:
I didn't actually mean creating the table definition.  I meant creating the table and populating it with data displayed hiearchically. In my example above, how would I create table 2's from table 1?  
0
 
YveauCommented:
OK ...

I used the Y table:
create table Y (ID int not null identity, Object varchar(10), Reference int, Type varchar(25))
alter table Y add constraint pkY primary key (ID)
alter table Y add constraint fkYY foreign key (Reference) references Y (ID)

and created old table:
create table old (ccd int, bu char(3), wg char(3))
insert into old values(123,'ABC','A11')
insert into old values(123,'DEF','A24')
insert into old values(456,'DDD','B17')
insert into old values(456,'EEE','B18')

Then fill Y with:
-- 1.
insert  into Y (Object, Reference, Type)
select  distinct ccd, null, 'company'
from    old
update  Y
set     Reference = ID
where   Reference is null
-- 2.
insert  into Y (Object, Reference, Type)
select  bu, Y.ID, 'Business Unit'
from    old
inner   join Y
on      old.ccd = Y.Object
-- 3.
insert  into Y (Object, Reference, Type)
select  wg, Y.ID, 'Workgroup'
from    old
inner   join Y
on      old.bu = Y.Object

and you'll get this from select * from Y:
ID          Object     Reference   Type
----------- ---------- ----------- -------------------------
1           123        1           company
2           456        2           company
3           ABC        1           Business Unit
4           DEF        1           Business Unit
5           DDD        2           Business Unit
6           EEE        2           Business Unit
7           A11        3           Workgroup
8           B17        5           Workgroup
9           A24        4           Workgroup
10          B18        6           Workgroup

Hope this helps ...
0
 
dentyneAuthor Commented:
That was great.  Thanks!  Do you think it is possible to create this as a view?  I'm thinking that this table would need to be refreshed quite a bit as data is added.
0
 
YveauCommented:
... don't think you can put this in a view ... Now you have the code, refreshing is a matter of seconds ...
Glad I could be of any help and thanks for the grade !
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now