Solved

Help creating SQL View

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

839 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