Please, read my comment to the question first.
Well, MSDN documentation is full of examples:
How to: Bind a TreeView to Data That Has an Indeterminable Depth[
^]
Walkthrough: Displaying Hierarchical Data in a TreeView Control[
^]
How to: Use a TreeView to Display Hierarchical Data[
^]
Please, follow the links to find out how to insert nodes to TreeView from hierarchical data using C# code.
As to the CTE... You can use CTE (recursive query) to determine the level of node depth, but your data have to be corrected. Please, study below example:
DECLARE @tmp TABLE([prod code] NVARCHAR(255), [comp code] NVARCHAR(255))
INSERT INTO @tmp ([prod code], [comp code])
VALUES('Root', NULL),
('Root', '21223'),
('21223', 'part1'),
('21223', 'part2'),
('part2', 'subpart'),
('part2', 'subpart2'),
('subpart', 'lowerlevel')
;WITH CTE AS
(
SELECT 1 AS NodeLevel, [prod code] AS NodeName
FROM @tmp
WHERE [comp code] IS NULL
UNION ALL
SELECT t1.NodeLevel + 1 AS NodeLevel, t2.[comp code] AS NodeName
FROM CTE AS t1 INNER JOIN @tmp AS t2 ON t1.NodeName = t2.[prod code]
WHERE t2.[comp code] IS NOT NULL
)
SELECT *
FROM CTE
Result:
NodeLevel NodeName
1 Root
2 21223
3 part1
3 part2
4 subpart
4 subpart2
5 lowerlevel
For further information about CTE, please see:
Recursive Queries Using Common Table Expressions[
^]
SQL SERVER - Introduction to Hierarchical Query using a Recursive CTE - A Primer - Journey to SQL Authority with Pinal Dave[
^]
CTE Recursive query for data hierarchy(Parent Child hierarchy)[
^]
Depth First Strategy with Hierarchical data using CTE (Common Table Expression) - SQL Server | The SQL Ideas[
^]
Try!
Submit an article or tip