Hierarchies data sort

  • I have 1 table which contains self reference(parent-child relationship with the table).

    below is a lookalike table sample

    IF OBJECT_ID('tempdb..#tmp1') IS NOT NULL

    DROP TABLE #tmp1

    CREATE TABLE #tmp1(ColID INT, ParentColID INT, ColVal VARCHAR(20) )

    INSERT#tmp1

    SELECT1,NULL, 'Geography' UNION

    SELECT2,1,'East' UNION

    SELECT3,1,'West' UNION

    SELECT4,1,'South' UNION

    SELECT5,1,'North' UNION

    SELECT6,2,'W' UNION

    SELECT7,2,'A' UNION

    SELECT8,3,'C' UNION

    SELECT9,4,'A' UNION

    SELECT10,4,'E' UNION

    SELECT11, 4,'B'

    i am trying to get the data as shown below... I used CTE but not working, help me out..

    ColIDParentColIDColVal

    1NULLGeography

    21East

    72A

    62W

    51North

    41South

    94A

    114B

    104E

    31West

    83C

    the data should be sorted by ColVal and group by ColID, ParentCOlID no Sort col available in table.

    Abhijit - http://abhijitmore.wordpress.com

  • WITH CTE(ColID,ParentColID,ColVal,fullpath) AS (

    SELECT ColID,ParentColID,ColVal,CAST(ColVal AS VARCHAR(MAX))

    FROM #tmp1

    WHERE ParentColID IS NULL

    UNION ALL

    SELECT p.ColID , p.ParentColID ,p.ColVal, c.fullpath+'/'+CAST(p.ColVal AS VARCHAR(MAX))

    FROM CTE c

    INNER JOIN #tmp1 p ON p.ParentColID=c.ColID)

    SELECT ColID,ParentColID,ColVal

    FROM CTE

    ORDER BY fullpath

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 2 posts - 1 through 2 (of 2 total)

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