Hierarchies data sort

• Abhijit More

  SSCertifiable

  Points: 6897

  More actions

  January 28, 2009 at 3:08 am

  #199254

  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

• Mark Cowne

  One Orange Chip

  Points: 26984

  More actions

  January 28, 2009 at 5:23 am

  #933709

  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