Hierarchy data to query

Transact-SQL
1

Hi,

I have the data as below,

Key Domain Parent Value
1 0 0 Region
2 0 0 Country
3 0 0 State
4 0 0 Locality
5 1 0 US
6 2 5 USA
7 3 6 California
8 4 7 Los Angeles

I need the query output as below

Region Country State Locality
US USA California Los Angeles

Can you please let me know the query to get this output. Thanks in advance

2

What have you tried up so far?
Is this school assignment?

3

Key Domain Parent Value
1 0 0 Region
2 0 0 Country
3 0 0 State
4 0 0 Locality
5 1 0 US
6 2 5 USA
7 3 6 California
8 4 7 Los Angeles
9 3 6 Albama

Here, we have the State Albama, but there is no locality for that. I want to display locality as NULL. I have achieved this with the below query. In my query, Region and country is repeating when i do union. Region and country details were already displayed in the output of the query while fetching the locality and state. But I dont want them to repeat it in the first 2 queries.

SELECT REG.Key AS REGION_ID,REG.Value AS REGION_NAME,
NULL AS COUNTRY_ID, NULL AS COUNTRY_NAME,
NULL AS STATE_ID,NULL AS STATE_NAME,
NULL AS LOC_ID,NULL AS LOC_NAME
FROM table REG
WHERE REG.DOMAIN=(SELECT KEY FROM table WHERE Value='REGION')
UNION
SELECT REG.Key AS REGION_ID,REG.Value AS REGION_NAME,
COU.Key AS COUNTRY_ID, COU.Value AS COUNTRY_NAME,
NULL AS STATE_ID,NULL AS STATE_NAME,
NULL AS LOC_ID,NULL AS LOC_NAME
FROM table REG JOIN table COU
ON COU.PARENT=REG.KEY
WHERE COU.DOMAIN=(SELECT KEY FROM table WHERE Value='COUNTRY')
UNION
SELECT REG.Key AS REGION_ID,REG.Value AS REGION_NAME,
COU.Key AS COUNTRY_ID, COU.Value AS COUNTRY_NAME,
STA.Key AS STATE_ID,STA.Value AS STATE_NAME,
NULL AS LOC_ID,NULL AS LOC_NAME
FROM table STA JOIN table COU
ON STA.PARENT=COU.KEY
JOIN table REG
ON COU.PARENT=REG.KEY
WHERE STA.DOMAIN=(SELECT KEY FROM table WHERE Value='STATE')
UNION
SELECT REG.Key AS REGION_ID,REG.Value AS REGION_NAME,
COU.Key AS COUNTRY_ID, COU.Value AS COUNTRY_NAME,
STA.Key AS STATE_ID,STA.Value AS STATE_NAME,
LOC.Key AS LOC_ID,LOC.Value AS LOC_NAME
FROM table LOC JOIN table STA
ON LOC.PARENT=STA.KEY
JOIN table COU
ON STA.PARENT=COU.KEY
JOIN table REG
ON COU.PARENT=REG.KEY
WHERE STA.DOMAIN=(SELECT KEY FROM table WHERE Value='Locality')

Can you help on this query to achieve it.

4 
select regval.[value] as region
      ,couval.[value] as country
      ,staval.[value] as state
      ,locval.[value] as locality
  from [table] as reg
       inner join [table] as cou
               on cou.[value]='Country'
              and cou.domain=0
              and cou.parent=0
       inner join [table] as sta
               on sta.[value]='State'
              and sta.domain=0
              and sta.parent=0
       inner join [table] as loc
               on loc.[value]='Locality'
              and loc.domain=0
              and loc.parent=0
       left outer join [table] as regval
               on regval.domain=reg.[key]
       left outer join [table] as couval
               on couval.domain=cou.[key]
              and couval.parent=regval.[key]
       left outer join [table] as staval
               on staval.domain=sta.[key]
              and staval.parent=couval.[key]
       left outer join [table] as locval
               on locval.domain=loc.[key]
              and locval.parent=staval.[key]
 where reg.[value]='Region'
   and reg.domain=0
   and reg.parent=0
1 Like