Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Location
    Gatineau, QC
    Posts
    4

    Unanswered: self-referencing tables and concatenation (sql question).

    Hello everyone,

    I have a database in SQL Server 6.5 with a table called tblCategory. There are three fields I care about in this table, namely:

    CategoryID (required) (auto-number)
    CategoryName (required)
    CategoryParentID (optional)

    The table references itself - that is, CategoryParentID is related to the CategoryID. If CategoryParentID is blank or 0, then this means it is a root node. Otherwise, it is a sub-category of its parent.

    What I need is a way of displaying the results of the query with the full "path" of a category. For example, given the following records (with the fields separated by slashes):

    CategoryID / CategoryName / CategoryParentID
    ---------------------------------------------------------
    1 / Computer / 0
    2 / Audio-Visual / 0
    3 / Peripheral / 0
    4 / IBM / 1
    5 / Sun / 1
    6 / Sony / 2
    7 / Panasonic / 3
    8 / NetVista / 4

    etc...I want the entire path of the thing to be printed, that is the name from the parent records needs to be concatenated with the current name with some delimiter (like a dot).

    So, my results should look like this:
    1 / Computer
    2 / Audio-Visual
    3 / Peripheral
    4 / Computer.IBM
    5 / Computer.Sun
    6 / Audio-Visual.Sony
    7 / Audio-Visual.Panasonic
    8 / Computer.IBM.NetVista

    I'm a beginner with SQL Server - I don't really know anything about stored procedures, but somehow think that there is a solution to this problem using stored procedures.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    Look for 'Expanding Hierarchies' in BOL and try your problem. If you get struck somewhere, let us know

    - CB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •