How to show hierarchial data in a dropdownlist

Question

I have a table in SQL Server 2005.

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.

Answer 1

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

Answer 2

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 ([email protected])

Answer 3

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;