• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • Last Modified:

Sql Syntax to blow the hierarchy of a table

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
MRPT
Asked:
MRPT
  • 3
  • 3
  • 2
1 Solution
 
sdstuberCommented:
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
@sdstuber, had I seen yours I would not have posted - sorry.  wasn't happy with mine anyway
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
sdstuberCommented:
no problem, 52 seconds is pretty much a simul-post in my book
0
 
MRPTAuthor Commented:
@sdstuber,

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

Thanks,
Shravan
0
 
PortletPaulCommented:
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
 
sdstuberCommented:
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
 
MRPTAuthor Commented:
Thanks a lot SDSTUBER.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now