Solved

Help with SQL view using SUM(CASE WEHN

Posted on 2009-05-18
3
838 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24415336
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
ID: 24416685
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
ID: 24423584
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Server 2012 r2 and SQL 2014 6 34
Format Output of Select Statement 2 38
What does "Between" mean? 6 47
DMV Script to find how many times statistics are utilized 2 30
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

738 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