Solved

Help creating SQL View

Posted on 2009-05-15
3
240 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
[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
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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

717 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