Help creating SQL View
Posted on 2009-05-15
Ive got a series of three table
column in teh table are simple
col1 = cc_id int identiy true
col2 = cc varchar
col1 = heir_id int identity true
col2 = heir
col1 = join_id int identity true
col2 = cc_id int
col3 = heir_id int
col4 = cc_id int
Basically I have a table of cost centers.
and a heirarchy table
All of the cost cneters have hierarchical relationships to other cost centers.
6 maximum level but not all have 6 levels.
So the tbl_heir has only 6 entrie period heir 1, heir 2, heri 3, heir 4, heir 5, heir 6
the cc has about 5000 values.
the join is essential a join for a cost center and the heir level cost cenetr of its related cost centers.
so it might look something like this
join_id cc_id heir_id cc_id
1 1 1 8
2 1 2 12
3 2 1 3
4 2 2 4
5 2 3 6
6 2 4 13
7 3 1 7
Now I need to construct a view that contains a colum for cost center (ever cost center in the tbl_cc) and then a colum for each of the possible heirarchies so basically twleve columns one for each value of the twelve and then a column for the corresponding cc entry for that heir level
So the join table above viewed through teh view instead looks like this
cc_id heir_1 heir_2 heir_3 heir_4 heir_5 heir_6
1 8 12
2 3 4 6 13
Is this possible and if so how to I contruct it..presumably I have to use criteria to do this but Im uncertain how to go about it.