How to create parent/child within a table?

Question

0.00/5 (No votes)

See more:

Hi,

I have a table with three columns in the following structure.

ContryCode	RegionCode	HotelCode
    
    IN	        DEL	        DL65
    IN	        DEL	        DL12
    IN	        AGR	        AG47
    SG    	    JAI	        JA30
    SG	        SIN	        SI21

I want to transform this data into Parent Child relationship. Expected result is

ID	 Value	ParentId	Parent
1	IN	    0	        
2	DEL	    1	        IN
3	DL65	2	        DEL
4	DL12	2	        DEL
5	AGR	    1	        IN
6	AG47	5	        AGR
7	SG	    0	        
8	JAI	    7	        SG
9	JA30	8	        JAI
10	SIN    	7	        SG
11	SI21	10	        SIN

What I have tried:

I have tried to manage via UNION ALL but result is not as expected and I can't form Parent child relationship. I have also tried with CTE recursive with no success.

Select CountryCode, RegionCode, HotelCode into #Tdest
From Dests A

Select DISTINCT CountryCode AS Value
From #Tdest A
union ALL
Select DISTINCT RegionCode AS Value
From #Tdest B
union ALL
Select DISTINCT HotelCode AS Value
From #Tdest c

Thanks in advance.

Posted 1-Jun-20 0:46am

Basil Pereira

Updated 1-Jun-20 10:41am

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

jsc42 · Answer 1 · 2020-06-01T01:32:00

The type of Parent/Child relationship that you are asking for is reasonable if all of the items were the same. For example, if you had an employee database, then an employee would have a manager, the manager is also an employee and he / she also has a manager.

However, in your case, you are mixing countries, region, and hotels which are not compatible. How, for example, would you know that the entry for DEL was referring to a region and not a country? What is to prevent you having a country that has a parent that is a hotel?

What you have is a simple hierarchy of objects, all one-to-many relationships. You should have a table of countries, a table of regions, and a table of hotels. The regions table should have a foreign key pointing to a primary key in the countries table, and the hotels table should have a foreign key pointing to the primary key in the regions table.

Maciej Los · Answer 2 · 2020-06-01T10:43:00

If you would like to create tree structure as follow:

- country1
  - region1
  + region2
    - hotel1
    - hotel2
- country2
  + region1
    - hotel1
    - hotel2
  - region2
--and so on...

you can achieve that by using below script:

SQL

--variables (type of table)
DECLARE @hotels TABLE (CountryCode VARCHAR(10),	RegionCode VARCHAR(10),	HotelCode VARCHAR(10))
DECLARE @places TABLE(PlaceID INT, PlaceCode VARCHAR(10), ParentID INT)

--insert source data
INSERT INTO @hotels(CountryCode, RegionCode, HotelCode)
VALUES('IN', 'DEL', 'DL65'), ('IN', 'DEL', 'DL12'),
('IN', 'AGR', 'AG47'), ('SG', 'JAI', 'JA30'), ('SG', 'SIN', 'SI21')

--insert data into destination table
INSERT INTO @places(PlaceID, PlaceCode, ParentID)
SELECT T.PlaceID, T.PlaceCode, NULL AS ParentID
FROM
(
	SELECT DISTINCT ROW_NUMBER() OVER(ORDER BY Deep, PC) PlaceID, PC AS PlaceCode, Deep
	FROM 
	(
		SELECT DISTINCT CountryCode AS PC, COUNT(CountryCode) AS Cnt, 1 AS Deep
		FROM @hotels
		WHERE CountryCode IS NOT NULL 
		GROUP BY CountryCode
		UNION ALL
		SELECT DISTINCT RegionCode AS PC, COUNT(RegionCode) AS Cnt, 2 AS Deep
		FROM @hotels
		WHERE RegionCode IS NOT NULL 
		GROUP BY RegionCode
		UNION ALL
		SELECT DISTINCT HotelCode AS PC, COUNT(HotelCode) AS Cnt, 3 AS Deep
		FROM @hotels
		WHERE HotelCode IS NOT NULL 
		GROUP BY HotelCode
	) DT
) T 

--update ParentID Of RegionCode for CountryCode
UPDATE t1 SET ParentID = t2.ParentID
FROM @places t1 INNER JOIN
(
	SELECT p.PlaceID As ParentID, h.RegionCode 
	FROM @places p INNER JOIN @hotels h ON p.PlaceCode = h.CountryCode 
) t2 ON t1.PlaceCode = t2.RegionCode  

--update ParentID Of HotelCode for RegionCode
UPDATE t1 SET ParentID = t2.ParentID
FROM @places t1 INNER JOIN
(
	SELECT p.PlaceID As ParentID, h.HotelCode 
	FROM @places p INNER JOIN @hotels h ON p.PlaceCode = h.RegionCode 
) t2 ON t1.PlaceCode = t2.HotelCode  


--display hierarchical data
;WITH CTE AS
(
	SELECT 1 AS LoopNo, PlaceID, CONVERT(VARCHAR(MAX), PlaceCode) AS FullPlaceCode
	FROM @places
	WHERE ParentID IS NULL
	UNION ALL
	SELECT LoopNo + 1 AS LoopNo, p.PlaceID, CONVERT(VARCHAR(MAX), CONCAT(c.FullPlaceCode, '->',  p.PlaceCode)) AS FullPlaceCode
	FROM CTE c INNER JOIN @places p ON c.PlaceID = p.ParentID 
)
SELECT PlaceID AS LastPlaceID, FullPlaceCode AS Hierarchy
FROM 
(
	SELECT PlaceID, FullPlaceCode, DENSE_RANK() OVER(ORDER BY LoopNo DESC) AS RowNo
	FROM CTE
	--OPTION (MAXRECURSION 0)
	--uncomment above line if you'll get an error message about recursive query exceeded the number of ...
) T
WHERE RowNo = 1
ORDER BY PlaceID

Result (of last SELECT statement):

LastPlaceID	Hierarchy
7	IN->AGR->AG47
8	IN->DEL->DL12
9	IN->DEL->DL65
10	SG->JAI->JA30
11	SG->SIN->SI21

Patrice T · Answer 3 · 2020-06-01T01:27:00

Quote:
How to create parent/child within a table?

Here is a fzew links, have nice reading:
SQL Queries to Manage Hierarchical or Parent-child Relational Rows in SQL Server[^]
sql server - Parent Child SQL Recursion - Stack Overflow[^]
Using a Parent Child Hierarchy in SQL Server to Implement a Custom Security Scheme[^]
Hierarchical Data (SQL Server) - SQL Server | Microsoft Docs[^]

[Update]

Quote:
I have a table with three columns in the following structure.

On second read, your data don't have a parent/child relation. Using parent/child relation is done for recursive relations like "is son of". Junior is your son, you are the con of dad, your dad is son of granddad.
your data have hierarchical data, like group name/sub group/detail.

How to create parent/child within a table?

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0