Re: Connect By in SQL Server 2000

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Mon, 4 Nov 2002 14:57:05 -0000
Message-ID: <3dc68ac1$0$8508$ed9e5944@reading.news.pipex.net>

"Simon" <aaronss_at_the-mdu.com> wrote in message news:f526ea06.0211040623.1195031b_at_posting.google.com...
> Simply, with regards to the application, I need to see how many
> children there are under the parent.

The attached is from books online for Transact-SQL tips. There are also a number of parent-child enhancements in analysis services (which you need Enterprise Edition for).

---

Expanding Hierarchies
Databases often store hierarchical information. For example, the following data is a hierarchical representation of regions of the world. This representation does not clearly show the structure implied by the data.

Parent                             Child
---------------------------------- ----------------------------------
World                              Europe
World                              North America
Europe                             France
France                             Paris
North America                      United States
North America                      Canada
United States                      New York
United States                      Washington
New York                           New York City
Washington                         Redmond

This example is easier to interpret:

World

   North America

      Canada
      United States
         Washington
            Redmond
         New York
            New York City
   Europe
      France
         Paris

The following Transact-SQL procedure expands an encoded hierarchy to any arbitrary depth. Although Transact-SQL supports recursion, it is more efficient to use a temporary table as a stack to keep track of all of the items for which processing has begun but is not complete. When processing is complete for a particular item, it is removed from the stack. New items are added to the stack as they are identified.

CREATE PROCEDURE expand (@current char(20)) as SET NOCOUNT ON
DECLARE @level int, @line char(20)
CREATE TABLE #stack (item char(20), level int) INSERT INTO #stack VALUES (@current, 1)
SELECT @level = 1

WHILE @level > 0
BEGIN
   IF EXISTS (SELECT * FROM #stack WHERE level = @level)

      BEGIN
         SELECT @current = item
         FROM #stack
         WHERE level = @level
         SELECT @line = space(@level - 1) + @current
         PRINT @line
         DELETE FROM #stack
         WHERE level = @level
            AND item = @current
         INSERT #stack
            SELECT child, @level + 1
            FROM hierarchy
            WHERE parent = @current
         IF @@ROWCOUNT > 0
            SELECT @level = @level + 1
      END
   ELSE
      SELECT @level = @level - 1

END -- WHILE
The input parameter (@current) indicates the place in the hierarchy to start. It also keeps track of the current item in the main loop.

The local variables used are @level, which keeps track of the current level in the hierarchy, and @line, which is a work area used to construct the indented line.

The SET NOCOUNT ON statement avoids cluttering the output with ROWCOUNT messages from each SELECT. Received on Mon Nov 04 2002 - 08:57:05 CST