如何显示一个DropDownList分层数据 [英] How to show hierarchial data in a dropdownlist

问题描述

我有一个表在SQL Server 2005。

I have a table in SQL Server 2005.

我想在一个DropDownList维修器材相同的层次结构，以显示所有域名。即

I want to show all domain name in a dropdownlist maintaing the same hierarchy. i.e

法结果
  工程结果
  --civil结果
   - 机械结果
  导师：
  --Dental结果
  ----腔结果
  --MBBS结果

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

我要追加 - 根据域级别。它可以使用一个SQL查询。
或者另外我可以有任何其他控件来显示此数据。

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.

推荐答案

在SQL Server 2005和了，你可以使用基于<递归的CTE（例如href=\"http://consultingblogs.emc.com/christianwade/archive/2006/09/20/SQL-Server-Standard-_2D00_-Recursive-Hierarchies-to-XML.aspx\"相对=nofollow>这篇文章）：

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;

这篇关于如何显示一个DropDownList分层数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！