How to simplify this SQL query in SQL Server?

Question

I am trying to get parent to child hierarchy. This is my query if any way to simplify this ...using this query only

SELECT   
    Chain0.ParId AS ParentId, 
    CASE
       WHEN Chain3.title <> ' '
          THEN Chain3.title + ' > ' + Chain2.title + ' > ' + Chain1.title + ' > ' + Chain0.title
       WHEN Chain2.title <> ' '
          THEN Chain2.title + ' > ' + Chain1.title + ' > ' + Chain0.title
       WHEN Chain1.title <> ''
          THEN Chain1.title + ' > ' + Chain0.title
       WHEN Chain0.title <> ' '
          THEN Chain0.title
    END AS title
FROM 
    (SELECT 
         T1.Id, T1.Title, T2.ParId  
     FROM 
         TestTable as T1 
     LEFT OUTER JOIN 
         TestTable2 as T2 ON T1.Id = T2.Id) Chain0
LEFT OUTER JOIN 
    (SELECT 
         T1.Id, T1.Title, T2.ParId  
     FROM 
         TestTable as T1 
     LEFT OUTER JOIN 
         TestTable as T2 ON T1.Id = T2.Id) Chain1 ON Chain0.ParId = Chain1.Id
LEFT OUTER JOIN 
    (SELECT 
         T1.Id, T1.Title, T2.ParId  
     FROM 
         TestTable as T1 
     LEFT OUTER JOIN 
         TestTable as T2 ON T1.Id = T2.Id) Chain2 ON Chain1.ParId = Chain2.Id
LEFT OUTER JOIN 
    (SELECT 
         T1.Id, T1.Title, T2.ParId  
     FROM 
         TestTable as T1 
     LEFT OUTER JOIN 
         TestTable as T2 ON T1.Id = T2.Id) Chain3 ON Chain2.ParId = Chain3.Id
LEFT OUTER JOIN 
    (SELECT 
         T1.Id, T1.Title, T2.ParId  
     FROM 
         TestTable as T1 
     LEFT OUTER JOIN 
         TestTable as T2 ON T1.Id = T2.Id) Chain4 ON Chain3.ParId = Chain4.Id

this my two tables testtable and testTable2...

 id title 
        1   test
        2   get
        3   this
        4   value

        id  text   parId
        1   test1   null    
        2   get1        1
        3   this1       2
        4   value1      3

Output:

ParentId    title
---------------------------
NULL        test
  1         test > get
  2         test > get > this
  3         test > get > this > value

Simplify this query I need output like this above format...

id title 1 test 2 get 3 this 4 value id text parId 1 test1 null 2 get1 1 3 this1 2 4 value1 3 — Abimanyu
– Abimanyu, Commented Aug 9, 2016 at 5:38
Please do not put code samples or sample data into comments - since you cannot format it, it's extremely hard to read it.... Instead: update your question by editing it to provide that additional information! Thank you. — marc_s
– marc_s, Commented Aug 9, 2016 at 5:42
There is a typo in the sample: it must be "TestTable2 as T2" throughout the query. — Y.B.
– Y.B., Commented Aug 9, 2016 at 10:16

Y.B. · Accepted Answer · 2016-08-09 15:49:53Z

2

This is done through Recursive CTE

With
TestTable (id, title) As (
    Select 1, 'test' Union All
    Select 2, 'get'  Union All
    Select 3 ,'this' Union All
    Select 4, 'value'
    ),
TestTable2 (id, text, parId) As (
    Select 1, 'test1' , null Union All
    Select 2, 'get1'  , 1    Union All
    Select 3, 'this1' , 2    Union All
    Select 4, 'value1', 3
    ),
RecursiveCTE As (
    Select
        Src.id,
        Ref.parId,
        CAST(Src.title As varchar(1024)) As title --< Types in the Union must be aligned
    From TestTable Src
    Inner Join TestTable2 Ref On Src.id = Ref.id AND Ref.parId Is NULL
    Union All
    Select
        Src.id,
        Ref.parId,
        CAST(Prev.title + ' > ' + Src.title As varchar(1024)) As title --<   ... aligned
    From TestTable Src
    Inner Join TestTable2 Ref On Src.id = Ref.id
    Inner Join RecursiveCTE Prev On Prev.id = Ref.parId --<       Recursive call to self
)
Select parId As ParentId, title From RecursiveCTE

Update: withough Recirsive CTE it can be:

With
TestTable (id, title) As (
    Select 1, 'test' Union All
    Select 2, 'get'  Union All
    Select 3 ,'this' Union All
    Select 4, 'value'
    ),
TestTable2 (id, text, parId) As (
    Select 1, 'test1' , null Union All
    Select 2, 'get1'  , 1    Union All
    Select 3, 'this1' , 2    Union All
    Select 4, 'value1', 3
    )
Select
    Ref1.parID As ParentId,
    COALESCE(Src4.title + ' > ', '') +
    COALESCE(Src3.title + ' > ', '') +
    COALESCE(Src2.title + ' > ', '') +
    Src1.title As title
From TestTable2 Ref1
Left Join TestTable2 Ref2 On Ref2.id = Ref1.parId
Left Join TestTable2 Ref3 On Ref3.id = Ref2.parId
Left Join TestTable2 Ref4 On Ref4.id = Ref3.parId
Left Join TestTable Src1 On Src1.id = Ref1.id
Left Join TestTable Src2 On Src2.id = Ref2.id
Left Join TestTable Src3 On Src3.id = Ref3.id
Left Join TestTable Src4 On Src4.id = Ref4.id

edited Aug 9, 2016 at 15:49

answered Aug 9, 2016 at 10:10

Y.B.

3,58618 silver badges24 bronze badges

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

2 Comments

Y.B. Over a year ago

Quite possible, though puzzled why?

Y.B. Over a year ago

Then you really should use recursion. Although of cause you can just replicate lines in the last query as needed. It's pretty straightforward to get to Src5, 6 and 7 with corresponding Ref5, 6 and 7.

Collectives™ on Stack Overflow

How to simplify this SQL query in SQL Server?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related