Solved

Sql Syntax to blow the hierarchy of a table

Posted on 2013-05-16
8
374 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
  • 3
  • 3
  • 2
8 Comments
 
LVL 73

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 48

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 48

Expert Comment

by:PortletPaul
ID: 39173700
@sdstuber, had I seen yours I would not have posted - sorry.  wasn't happy with mine anyway
0
 
LVL 73

Expert Comment

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

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 48

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 73

Accepted Solution

by:
sdstuber earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

705 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

19 Experts available now in Live!

Get 1:1 Help Now