Hi guys,
I have a table called hierarchy where I have son and parent column like this:
I also have a table with sons’ description:
So far, I made a hierarchy in the following SELF JOINs schema and simply adding columns representing another layer. The trick is that the requirement is to create it way differently, in one column where parents follow sons. Like this:
Do you have any idea how to handle this?
Please let me know how instead of multiple unions I can handle this.
Thanks.
JamesK
April 5, 2024, 12:31pm
2
Without some consumable data (like I have shown below), it is hard to give a reliable answer. But, something like below where I am using a recursive cte might be what you need. You might also want to investigate hierarchyid
DROP TABLE IF EXISTS #tmp;
CREATE TABLE #tmp(son INT, parent INT);
--- parent 1
-- children 2, 3
-- grandchildren 2/4, 2/5, 2/6, 3/7, 3/8
INSERT INTO #tmp (son, parent)
VALUES
(1,NULL),
(2,1), (3,1),
(4,2),(5,2),(6,2),
(7,3),(8,3);
;WITH cte AS
(
SELECT son, parent, SortingId = CAST(RIGHT(CONCAT(REPLICATE('0',10),son),10) AS VARCHAR(1024))
FROM
#tmp tp
WHERE
parent IS NULL
UNION ALL
SELECT tp.son, tp.parent,
CAST(
CONCAT(
c.SortingId,'/',
RIGHT(CONCAT(REPLICATE('0',10),tp.son),10)
)
AS VARCHAR(1024))
FROM
#tmp tp
INNER JOIN cte c ON
c.son = tp.parent
)
SELECT * from cte ORDER BY cte.SortingId;
@JamesK thank you very much for your input.
Following your logic, I need to flatter the hierarchy so that I have everything in one column. Considering your data I want to have everything in one column like this (based on numbers):
1 - parent
This sounds really tricky I am wondering whether I can do it quicker than simply UNION each and every select statement to get it in one column.
Thanks again.
JamesK
April 9, 2024, 6:00pm
4
It is a recursive cte.You will not need to write a union for each level. Notice that the code after the UNION ALL construct references the cte - sort of like a self-reference.
Google for recursive cte in SQL Server.
I already used recursive CTE (first screenshot) and I have the relationship in two columns.
Parent
I have a feeling that I totally do not understand this recursive CTE concept that's why I have tons of questions.
JamesK
April 13, 2024, 1:38pm
6
If you have not already done so, copy the code I posted to a query window and run it. You will see it is doing exactly what you are describing. Then change the insert statement to add more levels or children or whatever and in every case it should generate results that show the hierarchy in the desired order.
If it does not, post the example data set and query that someone can copy and run, like I did.
Yeah, I did. I added extra rows, I understand what is happening. I make it happen with the sample data (attached). The thing is how to translate it into one column vertically, not horizontally like I posted above based on your input.
1 - parent
Data:
Ifor
April 15, 2024, 12:29pm
8
Pepper1991:
Yeah, I did. I added extra rows, I understand what is happening. I make it happen with the sample data (attached). The thing is how to translate it into one column vertically, not horizontally like I posted above based on your input.
This is what @JamesK did. The other columns just show you where the order is coming from. If you just want one column just SELECT Son; you will still need to sort by SortingId.
