Prev: Eliminate #tmp table to single SQL Select statement
Next: Instance is Running
From: Servando Canales on 12 Feb 2010 16:27
Hi all,
Here is an example on some table that we have:

IF OBJECT_ID('TEST','U') IS NOT NULL DROP TABLE TEST;
GO
CREATE Table TEST ( Parent int, Child int, DataOrder int, Data nvarchar(10),
Rec_level int)
insert test
SELECT 0, 4, 1, ' 4.0.0.0', 1 UNION
SELECT 4, 7, 2, ' 4.2.0.0', 1 UNION
SELECT 4, 8, 1, ' 4.1.0.0', 1 UNION
SELECT 8, 3, 1, ' 4.1.1.0', 1 UNION
SELECT 4, 9, 3, ' 4.3.0.0', 1 UNION
SELECT 0, 5, 2, ' 5.0.0.0', 1 UNION
SELECT 5, 15, 1, ' 5.1.0.0', 1 UNION
SELECT 15, 20, 1, ' 5.1.1.0', 1 UNION
SELECT 20, 34, 1, ' 5.1.1.1', 1 UNION
SELECT 15, 21, 2, ' 5.1.2.0', 1 UNION
SELECT 21, 32, 1, ' 5.1.2.1', 1 UNION
SELECT 5, 16, 2, ' 5.2.0.0', 1 UNION
SELECT 5, 18, 3, ' 5.3.0.0', 1 UNION
SELECT 0, 10, 3, '10.1.0.0', 1

with RecursiveData as(
select a.Parent, a.Child, a.DataOrder, a.Data, Rec_level
from test a
--where a.Parent = 0
where a.Child = 5
UNION ALL
select a.Parent, a.Child, a.DataOrder, a.Data, a.Rec_level +1
from RecursiveData r
INNER JOIN test a
ON r.Child = a.Parent
)
select b.Parent, b.Child, b.DataOrder, b.Data from RecursiveData b

We would like the results sorted by the Parent-Child relationship but also
taking into account the DataOrder, let me explain how the output should be
just to make it clear :

Parent Child DataOrder Data
0 5 2 5.0.0.0
5 15 1 5.1.0.0
15 20 1 5.1.1.0
20 34 1 5.1.1.1
15 21 2 5.1.2.0
21 32 1 5.1.2.1
5 16 2 5.2.0.0
5 18 3 5.3.0.0

Is there a way to accomplish that?
Note:
The data column is only to show the order (I cannot use the Data column to
sort)

Disregard Rec_level ( I was trying to see how many levels deep for each of
the childs)

I appreciate any help or pointers.

Thank you
Servando Canales





From: Plamen Ratchev on 12 Feb 2010 17:01
Here is one solution:

;WITH RecursiveData AS (
SELECT Parent, Child, DataOrder, Data,
CAST(ROW_NUMBER() OVER(ORDER BY DataOrder) AS VARBINARY(MAX)) AS sort_path
FROM test
WHERE Child = 5
UNION ALL
SELECT a.Parent, a.Child, a.DataOrder, a.Data,
sort_path + CAST(ROW_NUMBER() OVER(ORDER BY a.DataOrder) AS BINARY(4))
FROM RecursiveData AS r
INNER JOIN test AS a
ON r.Child = a.Parent
)
SELECT Parent, Child, DataOrder, Data, sort_path
FROM RecursiveData
ORDER BY sort_path;

--
Plamen Ratchev
http://www.SQLStudio.com
From: Servando Canales on 16 Feb 2010 10:18
Thank you...this works like a charm.


"Plamen Ratchev" wrote:

> Here is one solution:
>
> ;WITH RecursiveData AS (
> SELECT Parent, Child, DataOrder, Data,
> CAST(ROW_NUMBER() OVER(ORDER BY DataOrder) AS VARBINARY(MAX)) AS sort_path
> FROM test
> WHERE Child = 5
> UNION ALL
> SELECT a.Parent, a.Child, a.DataOrder, a.Data,
> sort_path + CAST(ROW_NUMBER() OVER(ORDER BY a.DataOrder) AS BINARY(4))
> FROM RecursiveData AS r
> INNER JOIN test AS a
> ON r.Child = a.Parent
> )
> SELECT Parent, Child, DataOrder, Data, sort_path
> FROM RecursiveData
> ORDER BY sort_path;
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
> .
>
 | 
Pages: 1
Prev: Eliminate #tmp table to single SQL Select statement
Next: Instance is Running