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

x
?
Solved

Sql Syntax to blow the hierarchy of a table

Posted on 2013-05-16
8
Medium Priority
?
382 Views
Last Modified: 2013-05-24
Hello,

I have the Source data in the following way:
            
District_id      Area_id      Region_id
101                        201        301
102                        201        301
103                        (null)      301
104                        202        302
105                        203        302

I need to display the above hierarchy in the following format:

Target data Display      
Level      Sub_Level
201            101
201                102
null                103
202                104
203                105
301                 201
301            101
301            102
301            103
301            201
301             (null)
302            202
302            104
302            203
302            105

So this way, if I filter on Level = 301(Region_id from source), then I get all the sub levels i.e., the Districts and Areas that roll under 301

Please suggest.

Thanks
0
Comment
Question by:MRPT
[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
  • 3
  • 2
8 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39173691
SELECT CASE WHEN n IN (1, 2) THEN region_id ELSE area_id END,
       CASE WHEN n IN (1, 3) THEN district_id ELSE area_id END
  FROM source,
       (SELECT 1 n FROM DUAL
        UNION ALL
        SELECT 2 FROM DUAL
        UNION ALL
        SELECT 3 FROM DUAL)
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39173693
not sure this is right, but it appears to be a set of unions (5 records input, 15 records output) see http://sqlfiddle.com/#!4/d22b7/1

LAYER UPR_LEVEL LWR_LEVEL WITH_INDENT
1      301      (null)    .
1      301      201      . 201
1      301      201      . 201
1      302      203      . 203
1      302      202      . 202
2      301      103      .. 103
2      301      102      .. 102
2      301      101      .. 101
2      302      105      .. 105
2      302      104      .. 104
3      201      102      .. 102
3      201      101      .. 101
3      202      104      .. 104
3      203      105      .. 105
3     (null)    103      .. 103
select
  1 as layer
, region_id as upr_level
, area_id as lwr_level
, '. ' || to_char(area_id) as with_indent
from source 
union all
select
  2 as lvl
, region_id
, district_id
, '.. ' || to_char(district_id)
from source 
union all
select
  3 as lvl
, area_id as levl
, district_id as sub_level
, '.. ' || to_char(district_id)
from source 
order by 1,2,3 desc

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39173700
@sdstuber, had I seen yours I would not have posted - sorry.  wasn't happy with mine anyway
0
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.

 
LVL 74

Expert Comment

by:sdstuber
ID: 39173724
no problem, 52 seconds is pretty much a simul-post in my book
0
 

Author Comment

by:MRPT
ID: 39174694
@sdstuber,

I didn't quiet understand the logic in your query. Could you please explain?

Thanks,
Shravan
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39174756
I will offer an interpretation if that's ok,  full kudos to sdstuber of course...

source has 5 records
this is then cross joined to a small union of 3 records (created by the union)

the effect of  a cross join is a "cartesian product" (a matrix formed by the number of rows in one table multiplied by the number of rows in the other table)

5x3 = 15 (rows) as a result.

the 3 unioned rows each represent a logical "level" to be used for determining what will be in a column, this is given the alias "n"

so:

CASE WHEN n IN (1, 2) THEN region_id ELSE area_id END

when the "logical level" is either 1 or 2 output region_id otherwise output district_id

 CASE WHEN n IN (1, 3) THEN district_id ELSE area_id END

when the "logical level" is either 1 or 3 output district_id otherwise output area_id

please see: http://sqlfiddle.com/#!4/d22b7/4
where all the columns are displayed which may help in understanding.

note the ANSI syntax of a cross join is:

 FROM source
CROSS JOIN  (
        SELECT 1 n FROM DUAL
        UNION ALL
        SELECT 2 FROM DUAL
        UNION ALL
        SELECT 3 FROM DUAL)
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39174816
PortletPaul has provided a nice description of the syntax.

The idea behind my query was...
It looked like you had 3 conditions.

1 - Region + District
2 - Region + Area
3 - Area + District

The select from dual union gives me a set of "n" with the values 1,2,3

The unfiltered cartesian/cross join gives me all possible combinations; but yields all of the values for every combination, making them appear as duplicates

So, use CASE to return the specific values from each combination.

When n = 1,  return Region in first column, District in second column
when n = 2, return Region in first column, Area in second column
when n = 3, return Area in first column, district in second column
0
 

Author Closing Comment

by:MRPT
ID: 39194155
Thanks a lot SDSTUBER.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

618 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