Help with Table Flattening - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Help with Table Flattening

Author

Topic

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2011-10-15 : 02:44:19

I have the following table:

CREATE TABLE TABLE1(PARENT_ID VARCHAR(10),CHILD_ID VARCHAR(10))

INSERT INTO TABLE1 SELECT '0','39285'
INSERT INTO TABLE1 SELECT '0','56579'
INSERT INTO TABLE1 SELECT '0','58244'
INSERT INTO TABLE1 SELECT '0','60227'
INSERT INTO TABLE1 SELECT '0','7433'
INSERT INTO TABLE1 SELECT '7433','7434'
INSERT INTO TABLE1 SELECT '7434','7435'
INSERT INTO TABLE1 SELECT '7434','7438'
INSERT INTO TABLE1 SELECT '7434','7444'
INSERT INTO TABLE1 SELECT '7435','7436'
INSERT INTO TABLE1 SELECT '7438','7439'
INSERT INTO TABLE1 SELECT '7438','7441'
INSERT INTO TABLE1 SELECT '7444','7445'
INSERT INTO TABLE1 SELECT '7444','7448'
INSERT INTO TABLE1 SELECT '7445','7446'
INSERT INTO TABLE1 SELECT '7445','7447'
INSERT INTO TABLE1 SELECT '7448','7449'
INSERT INTO TABLE1 SELECT '7439','7440'
INSERT INTO TABLE1 SELECT '7441','7442'
INSERT INTO TABLE1 SELECT '7441','7443'
INSERT INTO TABLE1 SELECT '7436','7437'

I want to convert the given table into following table without using any other intermediate table:

0 39285
0 56579
0 58244
0 60227
0 7433 7434 7435 7436 7437
0 7433 7434 7438 7439 7440
0 7433 7434 7438 7441 7442
0 7433 7434 7438 7441 7443
0 7433 7434 7444 7445 7446
0 7433 7434 7444 7445 7447
0 7433 7434 7444 7448 7449

i.e. I am trying to flatten out the data..but dont know why this is killing me..my brain is cranking up here..pls try to help guys..thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-15 : 04:20:54

[code]
;With CTE
AS
(
SELECT Parent_ID,Child_ID, CAST(Parent_ID + ' ' + Child_ID AS varchar(max)) AS [path]
FROM TABLETEST1
WHERE PARENT_ID='0'
UNION ALL

SELECT t.Parent_ID,t.Child_ID,c.Path + ' ' + t.Child_ID
FROM TABLETEST1 t
INNER JOIN CTE c
ON c.CHILD_ID = t.PARENT_ID
)

select path
from
(
select dense_rank() over (partition by left(replace(path,'0 ','')+' ',charindex(' ', replace(path,'0 ','')+' ')-1) order by len(path) desc) as Rnk,Parent_ID,path from CTE
)t
where Rnk=1
order by Parent_ID

output
------------------------------------------------------------
0 39285
0 56579
0 58244
0 60227
0 7433 7434 7435 7436 7437
0 7433 7434 7438 7439 7440
0 7433 7434 7438 7441 7442
0 7433 7434 7438 7441 7443
0 7433 7434 7444 7445 7446
0 7433 7434 7444 7445 7447
0 7433 7434 7444 7448 7449

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources