How to show hierarchial data in a dropdownlist

I have a table in SQL Server 2005.

alt text http://www.techpint.com/sites/default/files/images/table.JPG

  • How to show blank record in sql if duplicate rows exists
  • Is ıt possible to create a temporary table in a View and drop it after select?
  • How to increment a secondary sequence number in a `delete from … into` query?
  • Determine table referenced in a view in SQL Server
  • How to Clear down Query Execution Statistics in SQL Server 2005/2008
  • SQL results grouped by Weeks, Day
  • I want to show all domain name in a dropdownlist maintaing the same hierarchy. i.e

    Law
    Engineering
    –civil
    –Mechanical
    Medical
    –Dental
    —-Cavity
    –MBBS

    I need to append ‘–‘ according to the domain level. Is it possible using a sql query.
    or alternatively can I have any other control to show this data.

  • Displaying Hashbytes value in SSRS matrix
  • Running 'SET' command in SQL, prior to SELECT
  • t-sql maximum date on self join
  • Update and function
  • LIKE work-around in SQL (Performance issues)
  • ASPNETDB.mdf for MySQL
  • 3 Solutions collect form web for “How to show hierarchial data in a dropdownlist”

    In SQL Server 2005 and up, you can use recursive CTEs (example based on this article):

    DECLARE @t AS TABLE (domainid INT NOT NULL, domainname VARCHAR(25) NOT NULL, parentdomainid INT NULL);
    INSERT INTO @t VALUES (1, 'Law', NULL);
    INSERT INTO @t VALUES (3, 'Engineering', NULL);
    INSERT INTO @t VALUES (4, 'Medical', NULL);
    INSERT INTO @t VALUES (6, 'Civil', 3);
    INSERT INTO @t VALUES (7, 'Mechanical', 3);
    INSERT INTO @t VALUES (8, 'Dental', 4);
    INSERT INTO @t VALUES (9, 'MBBS', 4);
    INSERT INTO @t VALUES (12, 'Cavity', 8);
    
    -- SELECT * FROM @t;
    
    WITH CTE(domainid, parentdomainid, domainname, Depth, SortCol)
                AS
                (
                  SELECT domainid, parentdomainid, domainname, 0, CAST(domainid AS varbinary(max))
                  FROM @t
                  WHERE parentdomainid IS NULL
                  UNION ALL
                  SELECT d.domainid, d.parentdomainid, d.domainname, p.Depth + 1,
                            CAST(SortCol + CAST(d.domainid AS binary(4)) AS varbinary(max))
                  FROM @t AS d
                            JOIN CTE AS p
                              ON d.parentdomainid = p.domainid
                )
                SELECT domainid, parentdomainid, domainname, Depth, REPLICATE('--', Depth) + domainname as displayname
                FROM CTE
                ORDER BY SortCol;
    

    1) you need to write a query to select all nodes with it’s level (Name, Level)
    2) convert level to a number of ‘-‘ and concatenate with Name string

    A query (I know about WITH) to select DomainID, Level

    CREATE PROCEDURE [GetDomainTree]
    (
    @topDomainID int
    ) with encryption
    AS
    BEGIN
    DECLARE @level int
    DECLARE @count int
    
    DECLARE @tempTable TABLE (DomainID int, [Level] int)
    
    INSERT INTO @tempTable
    (DomainID, [Level])
    VALUES
    (@topDomainID, 0)
    SET @count = 1
    SET @level = 0
    WHILE @count > 0
     BEGIN
      INSERT INTO @tempTable
      (DomainID, [Level] )
      (
       SELECT DomainsRealTable.DomainID,
           @level + 1,
         @topDomainID
       FROM  @tempTable domains, DomainsRealTable
       WHERE domains.[Level] = @level
          AND DomainsRealTable.ParentDomainID = domains.DomainID
      )
      SELECT @count = COUNT(*)
      FROM   @tempTable
      WHERE  [Level] = @level + 1
      SET @level = @level + 1
     END
    SELECT *
    FROM   @tempTable
    ORDER BY [Level]
    END
    

    Now, You can write a sql function which will take int parameter (level) and return nvarchar ‘-‘*Level and combine both queries to get the result you are want

    you can also write it in code

    psudo code, you get the idea

    function createlist(parent_id, indent)
    {
      if parent_id is null
      {
        select children_name, children_id from table where parent is null
      }
      else
      {
        indent += "--";
        select children_name, children_id from table where parent = parent_id
      }
    
      foreach result
      {
        write option value="children_id" +indent+children_name+ /option
        createlist(children_id, indent)
      }
    }
    
    createlist(null, "");
    

    regardes

    James Lin (guanfenglin@gmail.com)

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.