Hierarchical Query to Return All Children

Question

Hierarchical Query to Return All Children

Viewing 7 posts - 31 through 36 (of 36 total)

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

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

Andy Hyslop (7/24/2012)
However this is not the worst news, when I originally posted this topic I was told this was a 2008 instance, now been told its 2005 so can't use this datatype 🙁
Thanks again
Andy

In that case, I have a solution. I'll be back.

--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)

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 2

Here's one possible solution that works in 2K5 and up. As usual, the details of how it works are in the comments in the code.

--=====================================================================================================================

-- Create the test table. This is not a part of the solution (although the indexes might be).

--=====================================================================================================================

--===== Conditionally drop the test table to make reruns in SSMS easier.

IF OBJECT_ID('tempdb..#Temp','U') IS NOT NULL DROP TABLE #Temp;

DROP TABLE #Hierarchy

--===== Create the test table

CREATE TABLE #Temp

(

OrgUnitCode INT,

ChildOrgUnitCode INT PRIMARY KEY CLUSTERED

)

;

--===== Add an index for performance for when this gets big

CREATE INDEX IX_#Temp_OrgUnitCode

ON #Temp (OrgUnitCode)

;

--===== Populate the test table with test data

INSERT INTO #Temp

(OrgUnitCode, ChildOrgUnitCode)

SELECT 1, 2 UNION ALL

SELECT 2, 3 UNION ALL

SELECT 3, 4 UNION ALL

SELECT 4, 5 UNION ALL

SELECT 1, 6

;

--=====================================================================================================================

-- Solve the problem for SQL Server 2005 (works in 2k8 as well but doesn't need the HIERARCHYID datatype, etc)

--=====================================================================================================================

--===== Conditionally drop the test table to make reruns in SSMS easier.

IF OBJECT_ID('tempdb..#Hierarchy','U') IS NOT NULL DROP TABLE #Hierarchy;

WITH

rCteHierarchy AS

( --=== This figures out the hierarchy. We really need the hLevel of each row later on.

-- hPath is just for human understanding in this case. You can boost performance by commenting it out.

-- Since there can be multiple "trees" in this problem, we assign numbers to each tree in the forest.

SELECT OrgUnitCode, ChildOrgUnitCode,

hPath = CONVERT(VARCHAR(8000),ChildOrgUnitCode),

hTree = ROW_NUMBER() OVER(ORDER BY ChildOrgUnitCode),

hLevel = 1

FROM #Temp WHERE ChildOrgUnitCode NOT IN (SELECT OrgUnitCode FROM #Temp)

UNION ALL

SELECT t.OrgUnitCode, t.ChildOrgUnitCode,

hPath = CONVERT(VARCHAR(8000),t.ChildOrgUnitCode) + ',' + r.hPath,

hTree = r.hTree,

hLevel = r.hLevel + 1

FROM rCteHierarchy r

JOIN #Temp t

ON t.ChildOrgUnitCode = r.OrgUnitCode

) --=== This numbers the rows according to descending level and is partitioned by the hTree.

-- We put the result into a temp table for performance reasons because the next set

-- of CTE's would execute the above CTE more than once if we didn't.

SELECT *,

pRowNum = ROW_NUMBER() OVER (PARTITION BY hTree ORDER BY hTree ASC, hLevel DESC)

INTO #Hierarchy

FROM rCteHierarchy

;

-----------------------------------------------------------------------------------------------------------------------

-- Note that everything from here down could be converted to dynamic SQL to figure out the number of we need

-- in the output "automagically".

--===== Presets for display purposes. This will mess things up if you use a distributed transaction (ie. Linked Server)

SET ANSI_WARNINGS OFF;

--===== This is also for display purposes but won't ever mess anything up.

SET NOCOUNT ON; --Suppresses the auto-display of rowcounts.

WITH

cteExpand AS

( --=== This expands the number of rows using pRowNum to control how many duplicate rows to create

-- for each row in the hierarchy.

-- It also uses a correlated subquery which works very much like a CROSS APPLY to identify

-- the top level parent for each row.

SELECT Parent = (SELECT TOP 1 OrgUnitCode FROM #Hierarchy WHERE pRowNum = 1 AND hTree = h.hTree),

h.hTree,

h.pRowNum,

LineGroup = ROW_NUMBER()OVER(PARTITION BY h.hTree,h.pRowNum ORDER BY h.hTree,h.pRowNum DESC),

L = ChildOrgUnitCode

FROM #Hierarchy h

CROSS JOIN dbo.Tally t

WHERE t.N < h.pRowNum --Is a Zero Based Tally Table so has 1 extra count for "<"

),

cteGroup AS

( --=== This simply calculates the horizontal position of where things will go in the output.

SELECT *,

Position = ROW_NUMBER()OVER(PARTITION BY Parent,hTree,LineGroup ORDER BY Parent,hTree,LineGroup,pRowNum)

FROM cteExpand

) --=== This takes the vertical hierarchy and pivots it all into place according to the Position.

-- Notice how we group by Paren, hTree, and LineGroup (which line to put something on).

SELECT Parent,

L1 = MAX(CASE WHEN Position = 1 THEN L END),

L2 = MAX(CASE WHEN Position = 2 THEN L END),

L3 = MAX(CASE WHEN Position = 3 THEN L END),

L4 = MAX(CASE WHEN Position = 4 THEN L END)

FROM cteGroup

GROUP BY Parent,hTree,LineGroup

;

Here's the output...

Parent L1 L2 L3 L4

----------- ----------- ----------- ----------- -----------

1 2 3 4 5

1 3 4 5 NULL

1 4 5 NULL NULL

1 5 NULL NULL NULL

1 6 NULL NULL NULL

{EDIT} Send beer. I already have enough pretzels. 😀

--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)

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 3

Jeff thank-you! 🙂

I'll run it on my test box and let you know how I get on in a bit 😀

If this works I'll mail you a crate of your favourite !! 😀

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 4

Jeff where to start, this code seems to work perfectly, just had to change it to work for 10 levels 🙂

So 2 things:

A) Thank-you so much for helping me on this it really is appreciated

B) PM me an address where I can send your beers and your tipple of choice!! 😉

Thanks again

Andy

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 5

Andy Hyslop (7/26/2012)
Jeff where to start, this code seems to work perfectly, just had to change it to work for 10 levels 🙂
So 2 things:
A) Thank-you so much for helping me on this it really is appreciated
B) PM me an address where I can send your beers and your tipple of choice!! 😉
Thanks again
Andy

Thanks for the feedback, Andy. I was just kidding about the beer but I definitely appreciate the thought. If I started to get beer in the mail, my tea drinking neighbors might get a little jealous. 😛

Shifting gears, I'm glad you could easily change it to work with 10 levels. It probably means that I also did my job with the embedded documentation. If you need it to automatically figure out how many levels to work with, we can execute the second half as dynamic SQL with a couple of minor changes. I just got a bit lazy and posted a hardcoded version to show that it can be done.

--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)

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 6

I'm glad you could easily change it to work with 10 levels. It probably means that I also did my job with the embedded documentation

Yes Jeff that worked a treat, helped me immensely

If you need it to automatically figure out how many levels to work with, we can execute the second half as dynamic SQL with a couple of minor changes

No we should be fine here, I've been assured that the SAP system we are working off can only support a maximum of 10 levels, if they decide to change this then that will be a massive overhaul of the entire system and out of scope..

I just got a bit lazy and posted a hardcoded version to show that it can be done

Not at all Jeff, your efforts are really appreciated and enjoyed picking through your code 🙂

Thanks

Andy

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 7

Perfect. Thanks for the feedback, Andy.

--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)