Convert Oracle query to SQL Server?

Question

Oracle query is:

select  t3.id, 
        t4.caption || '>' || t3.caption  
from (
    SELECT  t1.id as id, 
            (t2.caption || '>' || t1.caption) as caption, 
            t2.pid as pid  
    FROM ParentChild T1, ParentChild T2 
    WHERE T1.pid = T2.id(+)
) t3, 
(
    SELECT  t1.id as id, 
            (t2.caption || '>' || t1.caption) as caption, 
            t2.pid as pid  
    FROM ParentChild T1, ParentChild T2 
    WHERE T1.pid = T2.id(+)
) t4 
where t3.pid = t4.id(+);

Output:

ID                     T4.CAPTION||'>'||T3.CAPTION
---------------------- ---------------------------
4                      a>b>c>d                    
3                      >a>b>c                     
1                      >>a                        
2                      >a>b                       
5                      >a>e

I need the equivalent SQL Server query...

Well, Oracle old style left join should be replaced with proper explicit left join, and oracle || string concatenation should be replaced with +. — Zohar Peled
– Zohar Peled, Commented Jun 30, 2016 at 9:35

Blank · Accepted Answer · 2016-06-30 09:35:45Z

2

Try following query;)

SELECT t3.id, t4.caption + '>' + t3.caption
FROM (
    SELECT t1.id as id, (t2.caption + '>' + t1.caption) as caption, t2.pid as pid 
    FROM ParentChild T1
    LEFT JOIN ParentChild T2
    ON T1.pid = T2.id) t3
LEFT JOIN (
    SELECT t1.id as id, (t2.caption + '>' + t1.caption) as caption, t2.pid as pid  
    FROM ParentChild T1
    LEFT JOIN ParentChild T2
    ON T1.pid = T2.id) t4 ON t3.pid = t4.id;

answered Jun 30, 2016 at 9:35

Blank

12.4k1 gold badge16 silver badges34 bronze badges

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

2 Comments

Developer Over a year ago

id (No column name) 1 NULL 2 NULL 3 NULL 4 a>b>c>d 5 NULL Am run this above query shown output like this

Blank Over a year ago

Is there the same data between two databases?

Collectives™ on Stack Overflow

Convert Oracle query to 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