How to flatten the hierarchial data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

How to flatten the hierarchial data

Author

Topic

edyl
Starting Member

35 Posts

Posted - 2015-04-16 : 00:08:00

Hello Everyone,

I have a situation where I would need to flatten the hierarchy within the data. It is best explained using examples. Please see below. Lets say I have a table with data as such:

Row Num....Parent....Child
1...............1A........2B
2...............2B........3C
3...............3C........4D
4...............4D........5E
5...............10Q.......11R
6...............11R.......12S
7...............100X......101Y
8...............101Y......102Z

On close observation, you will notice that there are 3 sets of data. Rows 1 thru 4 are related to each other thru "lineage" 1A = 2B = 3C=4D=5E. Similarly Rows 5 and 6, Rows 7 and 8 are related. What I want eventually want in my target table as the following.

Row Num....Parent....Child
1...............1A........2B
3...............1A........3C
4...............1A........4D
5...............1A........5E
6...............10Q.......10Q
7...............10Q.......11R
8...............10Q.......12S
9...............100X......100X
10..............100X......101Y
11..............100X......102Z

Instead of traversing thru the lineage, we would want each Child to be directly linked to the very first Parent. Can this be done? If so how can I achieve this? Please advise.

Thanks in Advance.

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-04-16 : 01:34:37

[code]
;WITH cteSample
AS
( SELECT 1 AS [Row Num],'1A' AS [Parent],'2B' AS [Child]
UNION ALL SELECT 2,'2B','3C'
UNION ALL SELECT 3,'3C','4D'
UNION ALL SELECT 4,'4D','5E'
UNION ALL SELECT 5,'10Q','11R'
UNION ALL SELECT 6,'11R','12S'
UNION ALL SELECT 7,'100X','101Y'
UNION ALL SELECT 8,'101Y','102Z')

,cteRec
AS (
SELECT C.Parent AS [Parent] , C.Parent AS [Child]
FROM cteSample AS C
LEFT JOIN cteSample AS P
ON P.[Child] = C.[Parent]
WHERE P.Parent IS NULL

UNION ALL

SELECT REC.Parent ,S.Child
FROM cteRec AS REC
INNER JOIN cteSample AS S
ON REC.[Child] = S.[Parent]
)
SELECT
ROW_NUMBER() OVER (ORDER BY Parent DESC, Child) AS Row_Num
,Parent
,Child
FROM
cteRec
[/code]

[code]
Row_Num Parent Child
1 1A 1A
2 1A 2B
3 1A 3C
4 1A 4D
5 1A 5E
6 10Q 10Q
7 10Q 11R
8 10Q 12S
9 100X 100X
10 100X 101Y
11 100X 102Z
[/code]

sabinWeb MCP

edyl
Starting Member

35 Posts

Posted - 2015-04-21 : 17:40:37

Hi Stepson,

This is absolutely genius.

Thanks!! But I am working with a Database (Netezza) that do not support Recursive CTEs. I have tried about almost everything but with very little success. Is there a way we can do this a cursor may be?

Regards

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-04-22 : 02:14:50

[code]
DECLARE @tSource TABLE
(rowNum INT
,Parent VARCHAR(50)
,Child VARCHAR(50));

DECLARE @tFinal TABLE
(rowNum INT IDENTITY(1,1) NOT NULL
,Parent VARCHAR(50)
,Child VARCHAR(50));

DECLARE @i INT = 1;
DECLARE @iNoRows INT =0;
DECLARE @vcParent VARCHAR(50) =''
,@vcChild VARCHAR(50) = '';

DECLARE
@vcP VARCHAR(50) = ''
,@vcC VARCHAR(50) =''
,@j INT = 0;

INSERT INTO @tSource (rowNum,Parent,Child)
SELECT 1 AS [Row Num],'1A' AS [Parent],'2B' AS [Child]
UNION ALL SELECT 2,'2B','3C'
UNION ALL SELECT 3,'3C','4D'
UNION ALL SELECT 4,'4D','5E'
UNION ALL SELECT 5,'10Q','11R'
UNION ALL SELECT 6,'11R','12S'
UNION ALL SELECT 7,'100X','101Y'
UNION ALL SELECT 8,'101Y','102Z'

SELECT @iNoRows = COUNT(*) FROM @tSource

WHILE @i<=@iNoRows
BEGIN
SELECT @vcParent = Parent
,@vcChild = Child
FROM @tSource
WHERE
rowNum = @i;

--find the parent
SET @j = @i;
SET @vcP = @vcParent;

INSERT INTO @tFinal (Parent,Child)
SELECT @vcParent,@vcParent
WHERE @vcParent NOT IN (SELECT Child FROM @tFinal WHERE Child = @vcParent)

WHILE @j >= 0
BEGIN
IF EXISTS( SELECT * FROM @tSource
WHERE Child = @vcP)
BEGIN
SELECT @vcP = Parent
,@vcC = Child
FROM @tSource
WHERE Child = @vcP;

END
SET @j = @j - 1;
END
INSERT INTO @tFinal (Parent,Child)
VALUES (@vcP,@vcChild)

SET @i= @i+ 1;

END

SELECT * FROM @tFinal
[/code]

[code]
rowNum Parent Child
1 1A 1A
2 1A 2B
3 1A 3C
4 1A 4D
5 1A 5E
6 10Q 10Q
7 10Q 11R
8 10Q 12S
9 100X 100X
10 100X 101Y
11 100X 102Z
[/code]

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources