Solved

Help creating SQL View

Posted on 2009-05-15
3
222 Views
Last Modified: 2012-05-07
Ive got a series of three table

tbl_cc
tbl_heir
tbl_join_cc_hier


column in teh table 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
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
3             7

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.


0
Comment
Question by:Prysson
3 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24401181
0
 
LVL 3

Accepted Solution

by:
GregTSmith earned 500 total points
ID: 24403237
You define tbl_join_cc_heir as having two "cc_id" columns, which cannot be true.  I've replaced the second column with "cc_heir_value?" in my attempt below.  Also, you could use the MAX aggregate instead of SUM, but that presumes only one record per combination of cc_id and heir_id is valid in the tbl_join_cc_heir table.  Using this method, be cautious to never supply an ELSE value other than NULL.  If you don't want to return a null value, use ISNULL around the aggregate.

(It should be noted, from my experience the method below is often faster than PIVOT, but never slower.)
SELECT 

  CC.cc_id, 

  SUM(CASE WHEN CCH.heir_id = 1 THEN CCH.cc_heir_value? ELSE NULL END) [heir_1], 

  SUM(CASE WHEN CCH.heir_id = 2 THEN CCH.cc_heir_value? ELSE NULL END) [heir_2], 

  SUM(CASE WHEN CCH.heir_id = 3 THEN CCH.cc_heir_value? ELSE NULL END) [heir_3], 

  SUM(CASE WHEN CCH.heir_id = 4 THEN CCH.cc_heir_value? ELSE NULL END) [heir_4], 

  SUM(CASE WHEN CCH.heir_id = 5 THEN CCH.cc_heir_value? ELSE NULL END) [heir_5], 

  SUM(CASE WHEN CCH.heir_id = 6 THEN CCH.cc_heir_value? ELSE NULL END) [heir_6] 

FROM 

  tbl_cc CC 

  LEFT JOIN tbl_join_cc_heir CCH ON CCH.cc_id = CC.cc_id 

GROUP BY 

  CC.cc_id   

Open in new window

0
 

Author Closing Comment

by:Prysson
ID: 31582122
Works great
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

746 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

16 Experts available now in Live!

Get 1:1 Help Now