CODEWITHSUNDEEP
sql-server
this.hart
this.hart

Reputation: 328

Make query like treeview

Brothers can you help me? Thanks 

                 Table A
       Id Name                 IdParent
       1   Operation            Null
       2   Developer             1
       3    Android              2
       4    IOS                  2

Expectes result:

       ID          Name
        1           +Operation
        2           +------ Developer
        3           +------------Android
        4           +------------ IOS

Upvotes: 2

Views: 99

Answers (3)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

DECLARE @mockup TABLE(Id INT, Name VARCHAR(100), IdParent INT);
INSERT INTO @mockup VALUES
 (1,'Operation',Null)
,(2,'Developer',1)
,(3,'Android',2)
,(4,'IOS',2);

--The query uses a recursive CTE and finally REPLICATE with the recursive level to add the number of hyphens...

WITH recCTE AS
(
    SELECT Id, Name, 1 AS Lvl, CAST(REPLACE(STR(ROW_NUMBER() OVER (ORDER BY Id),5),' ','0') AS VARCHAR(MAX)) AS Seq
    FROM @mockup 
    WHERE IdParent IS NULL

    UNION ALL

    SELECT m.Id,m.Name,r.Lvl +1,r.Seq + '.' + REPLACE(STR(ROW_NUMBER() OVER (ORDER BY m.Id),5),' ','0')
    FROM @mockup AS m
    INNER JOIN recCTE AS r ON m.IdParent=r.Id
)
SELECT *
      ,'+' + REPLICATE('-',Lvl*4) + Name 
FROM recCTE 
ORDER BY Seq

the result

+----+-----------+-----+----------------------+
| Id | Name      | Lvl | (Kein Spaltenname)   |
+----+-----------+-----+----------------------+
| 1  | Operation | 1   | +----Operation       |
+----+-----------+-----+----------------------+
| 2  | Developer | 2   | +--------Developer   |
+----+-----------+-----+----------------------+
| 3  | Android   | 3   | +------------Android |
+----+-----------+-----+----------------------+
| 4  | IOS       | 3   | +------------IOS     |
+----+-----------+-----+----------------------+

Upvotes: 2

John Cappelletti
John Cappelletti

Reputation: 81990

By adding a sequence during the recursive build, you can easily create the proper presentation sequence and nesting

Declare @YourTable table (id int,IdParent  int,Name varchar(50))
Insert into @YourTable values 
 ( 1, NULL,'Operation')
,( 2, 1   ,'Developer')
,( 3, 2   ,'Android')
,( 4, 2   ,'IOS')
,( 5, 1   ,'Poet')
,( 6, 5   ,'Limerick')
,( 7, 5   ,'Haiku')

Declare @Top    int         = null      --<<  Sets top of Hier Try 2
Declare @Nest   varchar(25) = '|-----'  --<<  Optional: Added for readability

;with cteP as (
      Select Seq  = cast(10000+Row_Number() over (Order by Name) as varchar(500))
            ,ID
            ,IdParent 
            ,Lvl=1
            ,Name 
      From   @YourTable 
      Where  IsNull(@Top,-1) = case when @Top is null then isnull(IdParent ,-1) else ID end
      Union  All
      Select Seq  = cast(concat(p.Seq,'.',10000+Row_Number() over (Order by r.Name)) as varchar(500))
            ,r.ID
            ,r.IdParent 
            ,p.Lvl+1
            ,r.Name 
      From   @YourTable r
      Join   cteP p on r.IdParent  = p.ID)
Select A.ID
      ,A.IdParent 
      ,A.Lvl
      ,Name = Replicate(@Nest,A.Lvl-1) + A.Name
 From ctep A
 Order By A.Seq

Returns

ID  IdParent    Lvl Name
1   NULL        1   Operation
2   1           2   |-----Developer
3   2           3   |-----|-----Android
4   2           3   |-----|-----IOS
5   1           2   |-----Poet
7   5           3   |-----|-----Haiku
6   5           3   |-----|-----Limerick

Upvotes: 3

Richard Hansell
Richard Hansell

Reputation: 5403

Here's another version:

WITH RawData AS (
    SELECT 1 AS Id, 'Operation' AS Name, CONVERT(INT, NULL) AS IdParent
    UNION ALL
    SELECT 2 AS Id, 'Developer' AS Name, 1 AS IdParent
    UNION ALL
    SELECT 3 AS Id, 'Android' AS Name, 2 AS IdParent
    UNION ALL
    SELECT 4 AS Id, 'IOS' AS Name, 2 AS IdParent),
Depth AS (
    SELECT
        Id,
        1 AS depth,
        IdParent
    FROM
        RawData
    UNION ALL
    SELECT
        d.Id,
        d.depth + 1,
        r.IdParent
    FROM
        Depth d
        INNER JOIN RawData r ON r.Id = d.IdParent),
MaxDepth AS (
    SELECT
        Id,
        MAX(depth) AS depth
    FROM
        Depth
    GROUP BY
        Id)
SELECT
    r.Id,
    '+' + REPLICATE('----', m.depth - 1) + r.Name AS Name
FROM 
    RawData r
    INNER JOIN MaxDepth m ON m.Id = r.Id;

Results:

Id  Name
1   +Operation
2   +----Developer
3   +--------Android
4   +--------IOS

Upvotes: 2

Related Questions