How to select path using sub query?

Question

I need to set path for each rows. Create table with parent, child and Path

CREATE TABLE CTE(
    id int,
    Title VARCHAR(20),
    ParentTitle VARCHAR(20),
    Path VARCHAR(20),    
)   

INSERT INTO CTE
VALUES
(1,'p1', 'Home', ''),
(2,'p1.1', 'p1', ''),
(3,'p1.1.1', 'p1', ''),    
(4,'p2', 'Home', ''), 
(5,'p2.1', 'p2', ''), 
(6,'p2.2', 'p2', ''),    
(7,'p2.3', 'p2.2', ''),
(8,'p3', 'Home', ''), 
(9,'p3.1', 'p3', ''), 
(10,'P2.2', 'p3.1', '')

I need output like the below table

╔════╦════════╦═════════════╦═══════════════════════╗
║ id ║ Title  ║ ParentTitle ║ Path                  ║
╠════╬════════╬═════════════╬═══════════════════════╣
║  1 ║ p1     ║ Home        ║ (home\p1)             ║
║  2 ║ p1.1   ║ p1          ║ (home\p1\p1.1)        ║
║  3 ║ p1.1.1 ║ p1          ║ (home\p1\p1.1\p1.1.1) ║
║  4 ║ p2     ║ Home        ║ (home\p2)             ║
║  5 ║ p2.1   ║ p2          ║ (home\p2\p2.1)        ║
║  6 ║ p2.2   ║ p2          ║ (home\p2\p2.2)        ║
║  7 ║ p2.2   ║ p2.2        ║ (home\p2\p2.2\2.3)    ║
║  8 ║ P3     ║ Home        ║ (home\p3)             ║
║  9 ║ p3.1   ║ p3          ║ (home\p3\p3.1)        ║
║ 10 ║ P2.2   ║ p3.1        ║ (home\p3\p3.1\p2.2)   ║
╚════╩════════╩═════════════╩═══════════════════════╝

I tried like this query, But I cannot get correct solution.

SELECT title,
       (SELECT CASE
                 WHEN c2.title != '' THEN c1.title + '\' + c2.title
               END
        FROM   cte C1
               LEFT JOIN cte C2
                      ON c1.title = c2.parenttitle
        WHERE  C.id = c1.id) AS root
FROM   cte C

codeproject.com/Articles/818694/…

sandeep rawat
– sandeep rawat

2016-11-05 06:06:40 +00:00
Commented Nov 5, 2016 at 6:06 — sandeep rawat
– sandeep rawat, Commented Nov 5, 2016 at 6:06

shA.t · Accepted Answer · 2016-11-05 10:20:02Z

1

I think you can use a query like this:

;WITH CTE AS (
    SELECT id, Title, ParentTitle, CAST('home\'+ Title as varchar(255)) As [path]
    FROM yourTable
    WHERE ParentTitle = 'Home'
    UNION ALL
    SELECT yourTable.id, yourTable.Title, yourTable.ParentTitle, CAST(CTE.[path] + '\' + yourTable.Title As varchar(255))
    FROM yourTable JOIN CTE
    ON CTE.Title = yourTable.ParentTitle AND yourTable.ParentTitle != 'Home'
)
SELECT *
FROM CTE
ORDER BY id;

`[SQL Fiddle Demo]`

Another way is to use UNION ALL with JOINs for each level like this:

SELECT t.id, t.Title, t.ParentTitle, 'home\' + t.Title [path]
FROM yourTable t
WHERE t.ParentTitle = 'Home'
UNION ALL
SELECT t1.id, t1.Title, t1.ParentTitle, 'home\' + t.Title +'\' + t1.Title
FROM yourTable t JOIN yourTable t1 ON t.Title = t1.ParentTitle
WHERE t.ParentTitle = 'Home'
UNION ALL
SELECT t2.id, t2.Title, t2.ParentTitle, 'home\' + t.Title +'\' + t1.Title + '\' + t2.Title
FROM yourTable t JOIN yourTable t1 ON t.Title = t1.ParentTitle JOIN yourTable t2 ON t1.Title = t2.ParentTitle
WHERE t.ParentTitle = 'Home';

edited Nov 5, 2016 at 10:20

answered Nov 5, 2016 at 7:45

shA.t

17.1k5 gold badges59 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Abimanyu Over a year ago

i need only using [email protected]

Abimanyu Over a year ago

"SELECT title, (SELECT CASE WHEN c2.title != '' THEN c1.title + '\' + c2.title END FROM cte C1 LEFT JOIN cte C2 ON c1.title = c2.parenttitle WHERE C.id = c1.id) AS root FROM cte C" like this..

shA.t Over a year ago

Using joins can be usable if depth is fixed, if yes then what is the max-depth value?

Abimanyu Over a year ago

max depth value is 5. @shA.t

shA.t Over a year ago

So, add another step to my second solution to achieve it ;).

Collectives™ on Stack Overflow

How to select path using sub query?

1 Answer 1

`[SQL Fiddle Demo]`

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related