Solved

Help creating SQL View

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Importing and exporting data Magento 1.x ? 4 69
Impove long SQL Stored Procedure Performance 14 69
Selection from table2 where criteria for table1 10 32
html input clean up 3 47
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

920 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