Selecting categories and subcategories from a table

• Atif

  Hall of Fame

  Points: 3578

  More actions

  January 8, 2010 at 10:55 pm

  #218106

  I have following table

  create table catProblem (CategID smallint,CategName varchar(30),parentid smallint)

  with following data in it

  Insert into catProblem (CategID,CategName,parentid)

  select 1, 'Electronics', 0 union all

  select 2, 'TV', 1 union all

  select 3, 'Panasonic', 2 union all

  select 4, 'LG', 2 union all

  select 5, 'LGnewmodel', 4 union all

  select 6, 'Toys', 0 union all

  select 7, 'GirlsToys', 5 union all

  select 8, 'BoysToys', 5 union all

  select 9, 'CriketRelated', 7 union all

  select 10, 'Dolls', 6

  GO

  I am required to select each category with its underlying categories separately. Like in following rows

  Electronic

  Electronic TV

  Electronic TV Panasonic

  Electronic TV LG

  Electronic TV LG LGNewModel

  Toys

  Toys GirlsToys

  Toys GirlsToys Dolls

  Toys BoysToys

  Toys BoysToys CriketRealted

  Please suggest some options for this task.

  Thanks

  DBDigger Microsoft Data Platform Consultancy.

• matzer

  SSCrazy Eights

  Points: 9727

  More actions

  January 9, 2010 at 1:58 am

  #1101325

  You can use a recursive CTE for this:

  WITH CTE(CategID, FullCategName, OrderString) AS

  (

  SELECT

  CategID, cast(CategName as varchar(max)),

  cast(cast(CategID as char(5)) as varchar(max)) OrderString

  FROM

  catProblem

  WHERE

  parentid = 0

  UNION ALL

  SELECT

  p.CategID, cast(c.FullCategName + ' ' + p.CategName as varchar(max)),

  c.OrderString + cast(p.CategID as char(5))

  FROM

  catProblem p

  JOIN

  CTE c ON c.CategID = p.parentid

  )

  SELECT

  *

  FROM

  CTE

  ORDER BY

  OrderString

  -- If the depth of the hierarchy is greater than 100 use OPTION

  -- to override the server-wide default recursion level

  -- OPTION (MAXRECURSION 0)

  Peter

• matzer

  SSCrazy Eights

  Points: 9727

  More actions

  January 9, 2010 at 2:05 am

  #1101327

  BTW, I change the testdata as follows because the parentid's were not correct.

  select 1, 'Electronics', 0 union all

  select 2, 'TV', 1 union all

  select 3, 'Panasonic', 2 union all

  select 4, 'LG', 2 union all

  select 5, 'LGnewmodel', 4 union all

  select 6, 'Toys', 0 union all

  select 7, 'GirlsToys', 6 union all

  select 8, 'BoysToys', 6 union all

  select 9, 'CriketRelated', 8 union all

  select 10, 'Dolls', 7

• Atif

  Hall of Fame

  Points: 3578

  More actions

  January 9, 2010 at 2:19 am

  #1101328

  Thanks for the help and correction.

  DBDigger Microsoft Data Platform Consultancy.

• Pedro DeRose [MSFT]

  Old Hand

  Points: 307

  More actions

  January 11, 2010 at 12:32 pm

  #1101995

  As a side note, if you often do descendant queries like these, it may be worth considering using hierarchyid instead of a parent-child table. In almost all cases, it should be easier and more performant.

  ---

  Pedro DeRose
  Program Manager, Microsoft SQL Server

  (Legalese: This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm.)