CTE RSS

• • Reply
  

  Vishal Bharg...

  Member

  4 Points

  16 Posts

  CTE

  Dec 13, 2012 07:12 AM|LINK

  I have a CTE Table Category and Sub Category With ID,Parent ID relation.

  I Wana to retrieve all Nested Sub Categories In Nested Ordered Manner.

  Some one Help me soon The Result Shuld be in Following Manners.

  Main Cat

  -- Sub A of Main

  ----Child Sub A of Sub A of Main

  ----Child Sub B of Sub A of Main

  ----Child Sub C of Sub A of Main

  -- Sub B of Main

  ----Child Sub A of Sub B of Main

  ----Child Sub B of Sub B of Main

  ----Child Sub C of Sub B of Main

  -- Sub C of Main

  ----Child Sub A of Sub C of Main

  ----Child Sub B of Sub C of Main

  ----Child Sub C of Sub C of Main

• • Reply
  

  silvioyf

  Participant

  1754 Points

  340 Posts

  Re: CTE

  Dec 13, 2012 11:03 AM|LINK

  Try something like this:

  with CTE_R as
  (
      select
          ID,
          Category,
          ParentID,
          CAST(Right(Replicate('0', 10) + CAST(ID as varchar(10)), 10) as varchar(100)) as OrderColumn
      from MyTable
      where ParentID = 0
      
      union all
      
      select
          t.ID,
          t.Category,
          t.ParentID,
          CAST(r.OrderColumn + Right(Replicate('0', 10) + CAST(t.ID as varchar(10)), 10) as varchar(100))
      from MyTable as t
      join CTE_R as r
          on r.ID = t.ParentID
  )
  
  select * from CTE_R
  order by OrderColumn

  Hope this helps.

  www.imoveisemexposicao.com.br
• • Reply
  

  yrb.yogi

  Star

  14460 Points

  2402 Posts

  Re: CTE

  Dec 13, 2012 11:05 AM|LINK

  One of the CTE example...

  Create Table #TempTable
  (
  	Id int,
  	Name varchar(20),
  	ParentId int,
  	Country varchar(20),
  	Col1 int,
  	Col2 int
  )
  insert into #TempTable
  select 1,'Steve',NULL,'UK',50,200
  Union All
  Select 2,'Neil',1,'US',35,600
  Union All
  Select 3,'Blair',2,'AU',41,250
  Union All
  Select 4,'Adrian',3,'FR',32,290
  Union All
  Select 5,'Ian',3,'UK',27,120
  Union All
  Select 6,'Matt',3,'DE',34,10
  Union All
  Select 7,'Steve',4,NULL,NULL,NULL
  
  --select * from #TempTable
  
  ;with CTE As
  (
  	select Id,Name,ParentId,Country,Col1,Col2,0 as [Level],
  	Cast(Id as varchar(max)) As MgrId,Cast(Name as varchar(max)) as MgrName
  	from #TempTable
  	where ParentId is null
  	
  	Union All
  	
  	select T.Id,T.Name,T.ParentId,T.Country,T.Col1,T.Col2,[Level]+1,
  	MgrId+'->'+Cast(T.Id as varchar(max)),MgrName+'->'+Cast(T.Name as varchar(max))
  	from #TempTable As T
  	Inner Join CTE On T.ParentId=CTE.Id
  )
  Select * from CTE
  
  drop table #TempTable
  
  --output
  --Id Name ParentId Country Col1 Col2       [Level] MgrId MgrName
  ---------------------------------------------------------------------------------------------
  --1 Steve NULL UK 50  200 0 1 Steve
  --2 Neil 1 US 35  600 1 1->2 Steve->Neil
  --3 Blair 2 AU 41  250 2 1->2->3 Steve->Neil->Blair
  --4 Adrian 3 FR 32  290 3 1->2->3->4 Steve->Neil->Blair->Adrian
  --5 Ian 3 UK 27  120 3 1->2->3->5 Steve->Neil->Blair->Ian
  --6 Matt 3 DE 34  10     3 1->2->3->6 Steve->Neil->Blair->Matt
  --7 Steve 4 NULL NULL NULL 4 1->2->3->4->7 Steve->Neil->Blair->Adrian->Steve

  
  
  

  Yogesh Bhadauriya
  .Net All About
  
  • Marked as answer by Chen Yu - MSFT on Dec 18, 2012 06:47 AM