Click here to Skip to main content
14,575,793 members
Rate this:
Please Sign up or sign in to vote.
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
Updated 1-Jun-20 10:41am
Rate this:
Please Sign up or sign in to vote.

Solution 2

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.
   
Comments
Maciej Los 1-Jun-20 13:33pm
   
Well explained!
+5!

But still it's possible to create tree structure/ hierarchical data. See my solution.
Basil Pereira 3-Jun-20 16:39pm
   
I am going to try your solution. I Appreciate your help.
Basil Pereira 3-Jun-20 16:37pm
   
@jsc42

Thanks for the explanation.I understand tables needs to be normalized but I am in a situation where I have to deal with this. Anyway thanks for your input.
Rate this:
Please Sign up or sign in to vote.

Solution 3

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:

--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
   
Rate this:
Please Sign up or sign in to vote.

Solution 1

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.
   
v2
Comments
Basil Pereira 3-Jun-20 16:40pm
   
I have tried most of these techniques. I am dealing with some unusual case here. Thanks anyway for sharing this.

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100