?
Solved

Sql Syntax to blow the hierarchy of a table

Posted on 2013-05-16
8
Medium Priority
?
381 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

762 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