Convert Oracle query to SQL Server 2008

Question

I am facing a problem to convert an Oracle query to its SQL Server 2008 version. In this Oracle query LISTAGG and connect by prior are used. I researched the web to find that WITH AS () is connect by prior equivalent in SQL Server 2008.

Also for LISTAGG alternate in SQL Server 2008 we can use STUFF(). But I'm facing difficulty in integrate these two logics into one. Please help me. The solution I tried seems to be wrong.

Here, is the Oracle query:

SELECT
    (LISTAGG(T1.c2, '/') WITHIN GROUP (ORDER BY T1.c1)) 
FROM 
    Table1 T1 
START WITH T1.c1 = T2.c3 
CONNECT BY PRIOR T1.c3 = T1.c1

This is the solution I tried:

with n(col1, col2) as 
(
    select T1.c1, '/' + T1.c2
    from table1 T1,
    where T1.c1 = T2.c3
    union ALL
    select T3.c1, '/' + T3.c2
    from table1 as T4,T3
    where T4.c3 = T3.c1  
)
select col2 from n;

Here in the both the queries T2 is a reference for a Table2 that is used in an outer query.

Ex:

Select 
.....,
.....,
.
.
(
  select (LISTAGG(T1.c2, '/') WITHIN GROUP (ORDER BY T1.c1)) 
  FROM Table1 T1 
  start with T1.c1 = T2.c3 
  connect by prior T1.c3 = T1.c1
) as ABC,
'
'
'
From
Table A,
Table B,
.
.
Table T2

Links I referred ::

score 0 · Answer 1 · answered Nov 20 '17 at 01:03

LISTAGG you can se here.

With you can do like this in SQL:

select * into #n from
(
    select T1.c1 col1, '/' + T1.c2 col2
    from table1 T1,
    where T1.c1 = T2.c3
    union ALL
    select T3.c1, '/' + T3.c2
    from table1 as T4,T3
    where T4.c3 = T3.c1  
)

Convert Oracle query to SQL Server 2008

1 Answers1