I've been tasked with converting a process from Oracle to Sql Server 2012.
I've converted most of the query over to SQL Server but am having a hard time translating over these queries to SQL Server logic.
SELECT sys_connect_by_path(field1, '/') || '/' || field2 || '/' path FROM data WHERE connect_by_isleaf = 1 connect BY prior field2 = field1 start WITH field1 NOT IN (SELECT field2 FROM data)
I know that I need to use a CTE for this example (possibly recursive) but I can't seem to get my logic correct
select field1, field2, regexp_substr(path, '[^/]+', 1, column_value) elem from grouped, table(cast(multiset(select level from dual connect by level <= regexp_count(path,'/')) as sys.odciNumberList)) where regexp_substr(path, '[^/]+', 1, column_value) is not null
select field1, field2, dense_rank() over (order by listagg(elem,',') within group (order by elem)) grp from split group by field1, field2
From what I understand, listagg in SQL is done by the STUFF function. That seems simple enough to understand but in this case I'm not sure how I make it work
Could I get some pointers?