Solved

Sql Syntax to blow the hierarchy of a table

Posted on 2013-05-16
8
379 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 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 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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 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 74

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
error in my cursor 5 51
Procedure syntax 5 50
DECLARATION OF CURSOR IS INCOMPLETE OR MALFORMED 5 47
Oracle performance tuning 2 32
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

697 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