Solved

Help with SQL view using SUM(CASE WEHN

Posted on 2009-05-18
3
833 Views
Last Modified: 2012-05-07


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
0
Comment
Question by:Prysson
  • 2
3 Comments
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
SELECT     tbl_cc.cc, 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
0
 

Author Comment

by:Prysson
Comment Utility
Not quite what I am looking for.

The code you posted display the cc value for the cc_id..which is fine...but what I need is for the heir columns to display the cc value that corresponds with the cc_heir_id primary key.

The cc_heir_id is in fact the same as cc_id..its a foreign kety constraint..which means that heach column above

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


has a value in the tbl_cc column that matches the id like below

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


your code displays the view like so

cc_id   heir_1  heir_2   heir_3  heir_4  heir_5  heir_6  
cc_A        8        12      NULL    NULL    NULL   NULL
cc_B        3        4         6          11         NULL   NULL        
cc_C         7       NULL  NULL    NULL    NULL   NULL
0
 

Accepted Solution

by:
Prysson earned 0 total points
Comment Utility
K..here is the correct solution

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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now