Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Help creating SQL View

Posted on 2009-05-15
3
Medium Priority
?
252 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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

604 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