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
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.
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)
精彩评论