Help with SQL view using SUM(CASE WEHN

Ive got a series of three table

tbl_cc
tbl_heir
tbl_join_cc_hier


column in the tables are simple

tbl_cc
col1 =  cc_id   int  identiy true
col2 = cc   varchar

tbl_heir
col1 = heir_id   int   identity true
col2 = heir

tbl_join_cc_heir
col1 = join_id  int  identity true
col2 = cc_id    int          (FK Constarint to tbl_cc.cc_id)
col3 = heir_id   int          (FK Constraint to  tbl_heir.heir_id)
col4 = heir_cc_id   int   (FK constraint with tbl_cc.cc_id)



tbl_cc are const centers

tbl_heir contains heirarchy levels  (there are only 6 levels)


Basically I have a table of cost centers.

and a heirarchy table

All of the cost centers have hierarchical relationships to other cost centers.

6 maximum level but not all have 6 levels.

So the tbl_heir has only 6 entries period   heir 1, heir 2, heri 3, heir 4, heir 5, heir 6

the tbl_cc has about 5000 values.

the join is essential a join for a cost center and the heir level cost center of its related cost centers.

so it might look something like this

join_id    cc_id     heir_id    cc_heir_id
1               1           1             8
2                1           2            12
3               2            1            3
4               2            2             4
5               2             3            6
6               2             4             11
7                3            1             7





Now I need to construct a view that contains a colum for cost center
and then a colum for each of the possible heirarchies  
so basically six columns one for each value of the 6 heir values and then a column for the corresponding cc entry for that heir level

The statement I am using now returns the primary keys as contained in the join table...

So it returns something lthat looks like this


cc_id   heir_1  heir_2   heir_3  heir_4  heir_5  heir_6  
1        8        12      NULL    NULL    NULL   NULL
2        3        4       6       11      NULL   NULL        
3        7        NULL    NULL    NULL    NULL   NULL


But the value of that heirachical column that I need displayerd isnt the primary key value but rather the value of the cc column that corresponds to it.


so lets say the tbl_cc has 12 entries

tbl_cc

cc_id    cc
1      cc_A
2      cc_B
3      cc_C
4      cc_D
5      cc_E
6      cc_F
7      cc_G
8      cc_H
9      cc_I
10      cc_J
11      cc_K
12      cc_L


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        cc_H    cc_L    
2        cc_C    cc_D     cc_F   cc_K          
3        cc_G

Could someone tell me how to adjust my view code to display the cc column value rather than the cc_id value as shown in the example above...

Here is my query code


SELECT     tbl_cc.cc_id, SUM(CASE WHEN dbo.tbl_join_cc_heir.heir_id = 1 THEN dbo.tbl_join_cc_heir.cc_heir_id ELSE NULL END) [heir1],
                      SUM(CASE WHEN dbo.tbl_join_cc_heir.heir_id = 2 THEN dbo.tbl_join_cc_heir.cc_heir_id ELSE NULL END) [heir2],
                      SUM(CASE WHEN dbo.tbl_join_cc_heir.heir_id = 3 THEN dbo.tbl_join_cc_heir.cc_heir_id ELSE NULL END) [heir3],
                      SUM(CASE WHEN dbo.tbl_join_cc_heir.heir_id = 4 THEN dbo.tbl_join_cc_heir.cc_heir_id ELSE NULL END) [heir4],
                      SUM(CASE WHEN dbo.tbl_join_cc_heir.heir_id = 5 THEN dbo.tbl_join_cc_heir.cc_heir_id ELSE NULL END) [heir5],
                      SUM(CASE WHEN dbo.tbl_join_cc_heir.heir_id = 6 THEN dbo.tbl_join_cc_heir.cc_heir_id ELSE NULL END) [heir6]
FROM         dbo.tbl_cc LEFT JOIN
                      tbl_join_cc_heir ON tbl_join_cc_heir.cc_id = tbl_cc.cc_id
GROUP BY tbl_cc.cc_id