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
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;
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
add a comment |
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
add a comment |
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
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
sql-server
asked Mar 25 at 14:50
titatovenaartitatovenaar
467 bronze badges
467 bronze badges
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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
add a comment |
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
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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
add a comment |
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
add a comment |
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
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
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
add a comment |
add a comment |
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.
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.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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