Split a column with data of a hierarchy of multiple parent/child levels into multiple columns (Recursive CTE hierarchy)SQLServer: What is the error with this stored procedure for creating dynamic pivot table?How to alter table when full text search is enabled?Cumulative sum & percentageCTE Recursion to get tree hierarchyexport to pipe delimited from SQL server table with column namesArithmetic overflow error converting datetime'sSSAS unbalanced Hierarchyconcatenating float with string resulting in errorUsing CTE to update multiple columns in table and loop on all rows in tableDouble rows in recursive CTE hierarchy

Multi tool use
Multi tool use

How do I ask a good question about a topic I am not completely familiar with?

My colleague is constantly blaming me for his errors

If I were to build a J3 cub twice the size of the original using the same CG would it fly?

What verb for taking advantage fits in "I don't want to ________ on the friendship"?

Do home values typically rise and fall consistently across different price ranges?

Can European countries bypass the EU and make their own individual trade deal with the U.S.?

Origin of the convolution theorem

Journal standards vs. personal standards

Discworld quote about an "old couple" who having said everything to each other, can finally go about living their lives

How useful would a hydroelectric plant be in the post-apocalypse world?

Word ending in "-ine" for rat-like

Does friction always oppose motion?

A* pathfinding algorithm too slow

Iterate over deepest values in a nested Association

Story where diplomats use codes for emotions

How to securely dispose of a smartphone?

Why would anyone even use a Portkey?

Why wasn't EBCDIC designed with contiguous alphanumeric characters?

The Lucas argument vs the theorem-provers--who wins and why?

Can dual citizens open crypto exchange accounts where U.S. citizens are prohibited?

Copy group of files (Filename*) to backup (Filename*.bak)

Two palindromes are not enough

Does a return economy-class seat between London and San Francisco release 5.28 tonnes of CO2 equivalents?

What election rules and voting rights are guaranteed by the US Constitution?



Split a column with data of a hierarchy of multiple parent/child levels into multiple columns (Recursive CTE hierarchy)


SQLServer: What is the error with this stored procedure for creating dynamic pivot table?How to alter table when full text search is enabled?Cumulative sum & percentageCTE Recursion to get tree hierarchyexport to pipe delimited from SQL server table with column namesArithmetic overflow error converting datetime'sSSAS unbalanced Hierarchyconcatenating float with string resulting in errorUsing CTE to update multiple columns in table and loop on all rows in tableDouble rows in recursive CTE hierarchy






.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;








1















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










share|improve this question




























    1















    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










    share|improve this question
























      1












      1








      1








      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










      share|improve this question














      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







      sql-server






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Mar 25 at 14:50









      titatovenaartitatovenaar

      467 bronze badges




      467 bronze badges






















          1 Answer
          1






          active

          oldest

          votes


















          1














          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





          share|improve this answer
























            Your Answer






            StackExchange.ifUsing("editor", function ()
            StackExchange.using("externalEditor", function ()
            StackExchange.using("snippets", function ()
            StackExchange.snippets.init();
            );
            );
            , "code-snippets");

            StackExchange.ready(function()
            var channelOptions =
            tags: "".split(" "),
            id: "1"
            ;
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function()
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled)
            StackExchange.using("snippets", function()
            createEditor();
            );

            else
            createEditor();

            );

            function createEditor()
            StackExchange.prepareEditor(
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: true,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: 10,
            bindNavPrevention: true,
            postfix: "",
            imageUploader:
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            ,
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            );



            );













            draft saved

            draft discarded


















            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55340521%2fsplit-a-column-with-data-of-a-hierarchy-of-multiple-parent-child-levels-into-mul%23new-answer', 'question_page');

            );

            Post as a guest















            Required, but never shown

























            1 Answer
            1






            active

            oldest

            votes








            1 Answer
            1






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            1














            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





            share|improve this answer





























              1














              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





              share|improve this answer



























                1












                1








                1







                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





                share|improve this answer















                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






                share|improve this answer














                share|improve this answer



                share|improve this answer








                edited Mar 25 at 15:53

























                answered Mar 25 at 15:24









                Daniel BrugheraDaniel Brughera

                1,4731 gold badge3 silver badges14 bronze badges




                1,4731 gold badge3 silver badges14 bronze badges


















                    Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.







                    Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.



















                    draft saved

                    draft discarded
















































                    Thanks for contributing an answer to Stack Overflow!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid


                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.

                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function ()
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55340521%2fsplit-a-column-with-data-of-a-hierarchy-of-multiple-parent-child-levels-into-mul%23new-answer', 'question_page');

                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    zYlIc5
                    ssGWLs

                    Popular posts from this blog

                    Kamusi Yaliyomo Aina za kamusi | Muundo wa kamusi | Faida za kamusi | Dhima ya picha katika kamusi | Marejeo | Tazama pia | Viungo vya nje | UrambazajiKuhusu kamusiGo-SwahiliWiki-KamusiKamusi ya Kiswahili na Kiingerezakuihariri na kuongeza habari

                    SQL error code 1064 with creating Laravel foreign keysForeign key constraints: When to use ON UPDATE and ON DELETEDropping column with foreign key Laravel error: General error: 1025 Error on renameLaravel SQL Can't create tableLaravel Migration foreign key errorLaravel php artisan migrate:refresh giving a syntax errorSQLSTATE[42S01]: Base table or view already exists or Base table or view already exists: 1050 Tableerror in migrating laravel file to xampp serverSyntax error or access violation: 1064:syntax to use near 'unsigned not null, modelName varchar(191) not null, title varchar(191) not nLaravel cannot create new table field in mysqlLaravel 5.7:Last migration creates table but is not registered in the migration table

                    은진 송씨 목차 역사 본관 분파 인물 조선 왕실과의 인척 관계 집성촌 항렬자 인구 같이 보기 각주 둘러보기 메뉴은진 송씨세종실록 149권, 지리지 충청도 공주목 은진현