CODEWITHSUNDEEP
sqlsql-servert-sql
Abimanyu
Abimanyu

Reputation: 511

How to select path using sub query?

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

Upvotes: 4

Views: 284

Answers (1)

shA.t
shA.t

Reputation: 16958

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';

Upvotes: 1

Related Questions