How to create a flat herarchy from a table containing parent and child relationship

Question

Post reply

How to create a flat herarchy from a table containing parent and child relationship

ritesh.sehgal

SSC Veteran

Points: 206
More actions
May 23, 2010 at 12:59 pm

#221438

need help in the following scenario,
Imagine there is table Company_Hierarchy having following columns
company_id,
company_name,
parent_company_id
for example:-
company_id company_name parent_company_id
1 India Comp 1
2 North 1
3 South 1
4 East 1
5 West 1
Using CTE I can get the hierarchy path,but i want the data in the following output:-
IDName Path Lvl1 Lvl2lvl3
1India Comp India Comp India CompNULLNULL
4 East India Comp>>EastIndia CompEastNULL
2 North India Comp>>NorthIndia CompNorthNULL
3 South India Comp>>SouthIndia CompSouthNULL
5 West India Comp>>WestIndia CompWestNULL

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 1

How many rows are in your real table?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ritesh.sehgal SSC Veteran Points: 206 More actions · Answer 2

The number of rows in the real table can be 10,000 to 30,000

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 3

@ritesh-2

I'll try to get to this tonight after work. You post has gone 17 hours with no coded answer. Please read the article at the first link in my signature line below for why that may be.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 4

Ok, here's what I came up with (@Jeff: pork chops please before noon your time so I'll have something for my BBQ 🙂 )

A few notes:

The first row for 'India Comp' to NULL is changed to use the standard recursive CTE.

I changed the path separator to a single character to use the standard split string function (but I added some validation to see if the separator is used within the company_name column).

Other than that I think up to 30K rows should still be manageable with a recursive CTE...

DECLARE @Company_Hierarchy TABLE

(

company_id INT,

company_name VARCHAR(30),

parent_company_id INT

)

INSERT INTO @Company_Hierarchy

SELECT 1 ,'India Comp', NULL UNION ALL

SELECT 2 ,'North', 1 UNION ALL

SELECT 3 ,'South', 1 UNION ALL

SELECT 4 ,'East', 1 UNION ALL

SELECT 5 ,'West', 1

DECLARE @separator CHAR(1)

SET @separator='>'

IF EXISTS (SELECT 1 FROM @Company_Hierarchy WHERE company_name LIKE '%'+@separator +'%')

SELECT 'name separator found in company_name'

;

WITH x(company_id, parent_company_id, company_name, path_, lvl) AS

(

SELECT company_id, parent_company_id, company_name, CAST(company_name AS VARCHAR(8000)), 0

FROM @Company_Hierarchy

WHERE parent_company_id IS NULL

UNION ALL

SELECT t.company_id, t.parent_company_id, t.company_name,x.path_+@separator+t.company_name, x.lvl+1

FROM @Company_Hierarchy t JOIN x ON x.company_id = t.parent_company_id

)

SELECT company_id,company_name, path_,

MAX(CASE WHEN itemnumber= 1 THEN item ELSE NULL END) AS lvl1,

MAX(CASE WHEN itemnumber= 2 THEN item ELSE NULL END) AS lvl2,

MAX(CASE WHEN itemnumber= 3 THEN item ELSE NULL END) AS lvl3

FROM x

CROSS APPLY

dbo.DelimitedSplit8K(x.path_,@separator)

GROUP BY company_id,company_name, path_

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ritesh.sehgal SSC Veteran Points: 206 More actions · Answer 5

thanks Jeff for the guideline... I ll be careful in posting the queries next time the way you have suggested.

Thanks Lutz for your code snippet.however your code cannot be executed as it is because seems you are using table DelimitedSplit8K but not sure what does this contain.

Also you are using case itemnumber which is not being used anywhere.

So i have modified the code to make it work but still not getting the desired results.

Following is the code

DECLARE @Company_Hierarchy2 TABLE

(

company_id INT,

company_name VARCHAR(30),

parent_company_id INT

)

INSERT INTO @Company_Hierarchy2

SELECT 1 ,'India Comp', NULL UNION ALL

SELECT 2 ,'North', 1 UNION ALL

SELECT 3 ,'South', 1 UNION ALL

SELECT 4 ,'East', 1 UNION ALL

SELECT 5 ,'West', 1

select * from @Company_Hierarchy2

DECLARE @separator CHAR(1)

SET @separator='>'

IF EXISTS (SELECT 1 FROM @Company_Hierarchy2 WHERE company_name LIKE '%'+@separator +'%')

SELECT 'name separator found in company_name'

;

WITH x(company_id, parent_company_id, company_name, path_, lvl) AS

(

SELECT company_id, parent_company_id, company_name, CAST(company_name AS VARCHAR(8000)), 0 as lvlnum

FROM @Company_Hierarchy2

WHERE parent_company_id IS NULL

UNION ALL

SELECT t.company_id, t.parent_company_id, t.company_name,x.path_+@separator+t.company_name, x.lvl+1 as lvlnum

FROM @Company_Hierarchy2 t JOIN x ON x.company_id = t.parent_company_id

)

SELECT company_id,company_name, path_ ,

MAX(CASE WHEN lvl= 0 THEN company_name ELSE NULL END) AS lvl0,

MAX(CASE WHEN lvl= 1 THEN company_name ELSE NULL END) AS lvl1,

MAX(CASE WHEN lvl= 2 THEN company_name ELSE NULL END) AS lvl2,

MAX(CASE WHEN lvl= 3 THEN company_name ELSE NULL END) AS lvl3

FROM x

--CROSS APPLY

--dbo.DelimitedSplit8K(x.path_,@separator)

GROUP BY company_id,company_name, path_

[mail] following is the data set I am getting now

company_idcompany_namepath_ lvl0 lvl1lvl2lvl3

1 India CompIndia CompIndia Comp NULLNULLNULL

2 North India Comp>NorthNULL NorthNULLNULL

3 South India Comp>SouthNULL SouthNULLNULL

4 East India Comp>EastNULL EastNULLNULL

5 West India Comp>WestNULL WestNULLNULL

ritesh.sehgal SSC Veteran Points: 206 More actions · Answer 6

Thanks Lutz, I got the DelimitedSplit8K function from this site itself and your code works perfectly well after using that.

Thanks a lot for the help

LutzM SSC Guru Points: 107049 More actions · Answer 7

Ooopps!! I'm sorry!! :blush:

I totally forgot that the split function I'm using isn't known by anybody by now... 😉

Glad you found it by yourself though... (Isn't this site an awesome source of knowledge?)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]