locked
Help..Help..Help..Help..Help..pls Help with Table Flattening RRS feed

  • Question

  • 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
    Saturday, October 15, 2011 6:46 AM

Answers

  • The following will give you the results but places them all into a single column:

    with x
    as
    (
    	select *, lvl = 1, cast ('' as varchar(max)) as Ancestry
    	from TABLE1
    	where PARENT_ID = '0'
    	union all
    	select t.*, x.lvl + 1, x.Ancestry + ' ' + x.PARENT_ID
    	from x
    	join TABLE1 t on t.PARENT_ID = x.CHILD_ID
    )
    select
    	case
    		when lvl = 1 then PARENT_ID + ' '
    		else ''
    	end
    +	ltrim (Ancestry + ' ' + CHILD_ID)
    from
    	x
    where not exists
    (
    	select
    		*
    	from
    		x x2
    	where
    		x2.PARENT_ID	= x.CHILD_ID
    )
    

     

     


    Tom
    SQL Server MVP
    Toronto, ON Canada
    • Proposed as answer by Tom Moreau Saturday, October 15, 2011 12:01 PM
    • Marked as answer by KJian_ Monday, October 24, 2011 5:15 AM
    Saturday, October 15, 2011 12:00 PM

All replies

  • Is there any pattern in output?

    Shatrughna.
    Saturday, October 15, 2011 8:01 AM
  • Thank you for the reply mate..I really appreciate that..I have got the result using following query:

    SELECT 0,
    lev01.CHILD_ID id_01,
    lev02.CHILD_ID id_02,
    lev03.CHILD_ID id_03,
    lev04.CHILD_ID id_04,
    lev05.CHILD_ID id_05
    FROM PEGGING lev01
    LEFT OUTER JOIN PEGGING lev02 ON lev01.CHILD_ID = lev02.parent_id
    LEFT OUTER JOIN PEGGING lev03 ON lev02.CHILD_ID = lev03.parent_id
    LEFT OUTER JOIN PEGGING lev04 ON lev03.CHILD_ID = lev04.parent_id
    LEFT OUTER JOIN PEGGING lev05 ON lev04.CHILD_ID = lev05.parent_id
    WHERE lev01.parent_id =0;

    which can be dynamically written as:


    DECLARE @MAX_LEVELS INT,@I INT,@CMD VARCHAR(MAX)
    --SET @MAX_LEVELS = SELECT MAX(LEVEL_ID) FROM TABLE1
    SET @MAX_LEVELS = 18
    SET @I=1
    SET @CMD = 'SELECT 0, '

    WHILE @I<@MAX_LEVELS
    BEGIN
        SET @CMD = @CMD + 'lev'+CAST(@I AS VARCHAR(MAX))+'.CHILD_ID id_'+CAST(@I AS VARCHAR(MAX))+','
        SET @I=@I+1
    END
        SET @CMD = @CMD + 'lev'+CAST(@MAX_LEVELS AS VARCHAR(MAX))+'.CHILD_ID id_'+CAST(@MAX_LEVELS AS VARCHAR(MAX))
        SET @CMD = @CMD + ' FROM TABLE1 lev1'
        --+CAST(@I AS VARCHAR(MAX))
        SET @I=2
        WHILE @I<@MAX_LEVELS
        BEGIN
        SET @CMD = @CMD + ' LEFT OUTER JOIN TABLE1 lev'+CAST(@I AS VARCHAR(MAX))+' ON lev'+CAST((@I-1) AS VARCHAR(MAX))+'.CHILD_ID = lev'++CAST(@I AS VARCHAR(MAX))+'.parent_id'
        SET @I=@I+1
        END
        SET @CMD = @CMD + ' LEFT OUTER JOIN TABLE1 lev'++CAST(@MAX_LEVELS AS VARCHAR(MAX))+ ' ON lev'+CAST((@I-1) AS VARCHAR(MAX))+'.CHILD_ID = lev'++CAST(@MAX_LEVELS AS VARCHAR(MAX))+'.parent_id'
        SET @CMD = @CMD + ' WHERE lev1.parent_id =0'
    PRINT @CMD

    But the query is taking loooooooooong time for millions of records..any way to optimize it?
    Saturday, October 15, 2011 8:41 AM
  • Thanks for sharing your solution.
    Shatrughna.
    Saturday, October 15, 2011 8:50 AM
  • Hi  rock !

    You may check this out;
       
     

    ;WITH CTE (PARENT_ID,CHILD_ID,ChildParentID)AS
    (
    SELECT CAST(PARENT_ID AS VARCHAR(MAX)) AS PARENT_ID, CAST(CHILD_ID AS VARCHAR(MAX)) AS CHILD_ID, CAST(PARENT_ID AS VARCHAR(MAX)) + ',' + CAST(CHILD_ID AS VARCHAR(MAX)) AS ChildParentID
    FROM #TABLE1
    WHERE PARENT_ID = 0
    UNION ALL
    SELECT CAST(T.PARENT_ID AS VARCHAR(MAX)) AS PARENT_ID, CAST(T.CHILD_ID AS VARCHAR(MAX)) AS CHILD_ID, CAST(C.ChildParentID AS VARCHAR(MAX)) + ',' + CAST(T.CHILD_ID AS VARCHAR(MAX)) AS ChildParentID
    FROM CTE C
    INNER JOIN #TABLE1 T ON T.PARENT_ID = C.CHILD_ID 
    )
    SELECT * FROM CTE
    


    Note : This solution will work for MS SQL 2005 and above.


    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks, Hasham

    Saturday, October 15, 2011 11:06 AM
    Answerer
  • The following will give you the results but places them all into a single column:

    with x
    as
    (
    	select *, lvl = 1, cast ('' as varchar(max)) as Ancestry
    	from TABLE1
    	where PARENT_ID = '0'
    	union all
    	select t.*, x.lvl + 1, x.Ancestry + ' ' + x.PARENT_ID
    	from x
    	join TABLE1 t on t.PARENT_ID = x.CHILD_ID
    )
    select
    	case
    		when lvl = 1 then PARENT_ID + ' '
    		else ''
    	end
    +	ltrim (Ancestry + ' ' + CHILD_ID)
    from
    	x
    where not exists
    (
    	select
    		*
    	from
    		x x2
    	where
    		x2.PARENT_ID	= x.CHILD_ID
    )
    

     

     


    Tom
    SQL Server MVP
    Toronto, ON Canada
    • Proposed as answer by Tom Moreau Saturday, October 15, 2011 12:01 PM
    • Marked as answer by KJian_ Monday, October 24, 2011 5:15 AM
    Saturday, October 15, 2011 12:00 PM