Styjun

I have the following problem I like to solve in my SQL query. I looked up many child/parent and multiple levels of hierarchy topics, and although I came quite far, somehow I cannot get (yet) to the best solution.

I have a table that has within one column a hierarchy, and I would like to pivot this hierarchy. I came quite far but now I got stuck since I have unnecessary double rows in my end result AND since the levels of the hierarchy mix up in the end result. First I will explain some more about the current data set, and after that I will explain how I got to my current result.

I have a table called PMEGROUND. This table consists of data of flats, neighborhoods and cities. A flat, neighborhood or city always have a unique OBJECTID and GROUNDID.

Within the GROUNDID’s there are flats, neighborhoods and cities, all within the same column. However they can have a MAINGROUNDID which points to the parent of the GROUNDID. So flats fall under neighborhoods, neighborhoods under cities; BUT NOW flats also fall under cities. This is a fraction of my current dataset:

 CREATE TABLE PMEGROUND (
 OBJECTID nvarchar(20),
 GROUNDID nvarchar(20),
 MAINGROUNDID nvarchar(20));

 INSERT INTO PMEGROUND (ObjectId, GroundId, MaingroundId)
 VALUES
 ('2','1',''), --City
 ('3','101','1'), -- Neighborhood
 ('4','10101','101'), -- Flat
 ('5','10102','101'),
 ('6','10103','101'),
 ('7','10104','101'),
 ('8','10105','101'),
 ('10','102','1'),
 ('11','10201','102'),
 ('12','10202','102'),
 ('13','10203','102'),
 ('14','10204','102'),
 ('16','103','1'),
 ('17','10301','103'),
 ('18','10302','103');

Important to know that it is not always the case that the Flats have 5 characters and the Neighborhood only 3.

I have been puzzling a while and so far I really got stuck with the following code. It does create a hierarchy, but:
1) it mixes the levels;
2) it leaves the original table to be selected as well somehow, so it looks like it ADDS the ‘spreaded columns hierarchy’ to the original table content. The rows are double, while it should have 1 row for every unique ObjectId. I tried Union instead of Union All, but double rows remain.

;with cteP as (
 Select GROUNDID
 ,OBJECTID
 ,MAINGROUNDID 
 ,PathID = cast(GROUNDID as varchar(max))
 From PMEGROUND
 Where REPLACE(ltrim(rtrim(MAINGROUNDID)),' ',NULL) is Null
 Union All
 Select GROUNDID = r.GROUNDID
 ,OBJECTID = r.OBJECTID
 ,MAINGROUNDID = r.MAINGROUNDID 
 ,PathID = p.PathID+concat(',',cast(r.GROUNDID as varchar(max)))
 From PMEGROUND r
 Join cteP p on r.MAINGROUNDID = p.GROUNDID)
Select A.GROUNDID
 ,OBJECTID
 ,B.*
From cteP A
Cross Apply (
 Select Lvl1 = xDim.value('/x[1]','varchar(50)')
 ,Lvl2 = xDim.value('/x[2]','varchar(50)')
 ,Lvl3 = xDim.value('/x[3]','varchar(50)')
 ,Lvl4 = xDim.value('/x[4]','varchar(50)')
 From ( values (cast('<x>' + replace(PathID,',','</x><x>')+'</x>' as xml))) B(xDim)
 ) B
Order By PathID

Now I want to have this hierarchy no longer in 2 columns, but I want to have spread it out into multiple columns, the end result should look exactly like this:

 CREATE TABLE PMEGROUNDFIX (
 OBJECTID nvarchar(20),
 Lvl1 nvarchar(20),
 Lvl2 nvarchar(20),
 Lvl3 nvarchar(20),
 Lvl4 nvarchar(20));

 INSERT INTO PMEGROUNDFIX (ObjectId, Lvl1, Lvl2, Lvl3, Lvl4)
 VALUES
 ('2','','','1',''), /*City*/
 ('3','','101','1',''), /*Neighborhood*/
 ('4','10101','101','1',''), /*Flat*/
 ('5','10102','101','1',''),
 ('6','10103','101','1',''),
 ('7','10104','101','1',''),
 ('8','10105','101','1',''),
 ('10','','102','1',''),
 ('11','10201','102','1',''),
 ('12','10202','102','1',''),
 ('13','10203','102','1',''),
 ('14','10204','102','1',''),
 ('16','','103','1',''),
 ('17','10301','103','1',''),
 ('18','10302','103','1','');

But how it is currently looking, is the following:

 CREATE TABLE PMEGROUNDWRONG (
 OBJECTID nvarchar(20),
 Lvl1 nvarchar(20),
 Lvl2 nvarchar(20),
 Lvl3 nvarchar(20),
 Lvl4 nvarchar(20));

 INSERT INTO PMEGROUNDWRONG (ObjectId, Lvl1, Lvl2, Lvl3, Lvl4)
 VALUES
 ('2','1','','',''),
 ('3','101','1','',''),
 ('3','101','','',''),
 ('4','10101','101','1',''),
 ('4','10101','','',''),
 ('5','10102','101','1',''),
 ('5','10102','','',''),
 ('6','10103','101','1',''),
 ('6','10103','','',''),
 ('7','10104','101','1',''),
 ('7','10104','','',''),
 ('8','10105','101','1',''),
 ('8','10105','','',''),
 ('10','102','1','',''),
 ('10','102','','',''),
 ('11','10201','102','1',''),
 ('11','10201','','',''),
 ('12','10202','102','1',''),
 ('12','10202','','',''),
 ('13','10203','102','1',''),
 ('13','10203','','',''),
 ('14','10204','102','1',''),
 ('14','10204','','',''),
 ('16','103','1','',''),
 ('16','103','','',''),
 ('17','10301','103','1',''),
 ('17','10301','','',''),
 ('18','10302','103','1',''),
 ('18','10302','','','');

So as you can see the dataset given above mixes hierarchy levels and it somehow has double rows.

Does anyone know what important thing I am missing here?

Kind regards,
Igor

Your xDim positions are on the oposite direction, also, your filter to start the recursive CTE was wrong, that's why you was getting duplicates

;with cteP as (
 Select GROUNDID
 ,OBJECTID
 ,MAINGROUNDID 
 ,PathID = cast(GROUNDID as varchar(max))
 From #PMEGROUND
 Where NULLIF(MainGroundID, '') IS NULL
 Union All
 Select GROUNDID = r.GROUNDID
 ,OBJECTID = r.OBJECTID
 ,MAINGROUNDID = r.MAINGROUNDID 
 ,PathID = p.PathID+concat(',',cast(r.GROUNDID as varchar(max)))
 From #PMEGROUND r
 Join cteP p on r.MAINGROUNDID = p.GROUNDID)
Select A.GROUNDID
 ,OBJECTID
 ,B.*
 ,PathID
From cteP A
Cross Apply (
 Select Lvl1 = xDim.value('/x[3]','varchar(50)')
 ,Lvl2 = xDim.value('/x[2]','varchar(50)')
 ,Lvl3 = xDim.value('/x[1]','varchar(50)')
 ,Lvl4 = xDim.value('/x[4]','varchar(50)')
 From ( values (cast('<x>' + replace(PathID,',','</x><x>')+'</x>' as xml))) B(xDim)
 ) B
Order By PathID