CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqlrecursive-query
nzsquall
nzsquall

Reputation: 405

How to do a recursive query

I have a database table looks like this:

EVENT_ID TEXT_FRO TEXT_TO
55001              05
55001    05        10
55001    10        15
55001    15        20
55001    20        30
56215    06        11
56215    11        22

I need to write a query (or a SP) to produce a result set to list all movements for each distinct event_ID which looks like this:

Event ID Movements
55001    05 10 15 20 30
56215    06 11 22

How can I do that?

*Edit to simplify the example

Upvotes: 1

Views: 239

Answers (2)

James L.
James L.

Reputation: 9453

It is pretty straight forward if you use a function and a cursor:

use tempdb
go

create table tmp (
  EVENT_ID int, 
  TEXT_FRO varchar(10), 
  TEXT_TO varchar(10)
)
go

insert into tmp values
(55001, NULL, '05'),
(55001, '05', '26'),
(55001, '26', '28'),
(55001, '28', '27'),
(55001, '27', '26'),
(55001, '26', '27'),
(55001, '27', '28'),
(55001, '28', '30'),
(55001, '30', '40'),
(56215, '06', '11'),
(56215, '11', '22')
go

You have to create a function to assemble the concatenated string:

create function fnConcat (@id int) returns varchar(255) as
begin
  declare @rtn varchar(255) = '', @fro varchar(10), @to varchar(10), @cnt int = 1

  declare cr cursor local for
    select TEXT_FRO, TEXT_TO
    from   tmp
    where  EVENT_ID = @id

  open cr
  fetch next from cr into @fro, @to

  while @@fetch_status = 0
  begin
    if @cnt = 1 and @fro is not null
      set @rtn = @rtn + @fro + ' '

    set @rtn = @rtn + @to + ' '

    set @cnt = @cnt + 1
    fetch next from cr into @fro, @to
  end

  close cr
  deallocate cr

  set @rtn = left(@rtn, datalength(@rtn) - 1)

  return @rtn
end
go

It is more efficient if you only call the function one time per unique EVENT_ID, so we select the distinct EVENT_ID in a sub-query:

select x.EVENT_ID as [Event ID], dbo.fnConcat(x.EVENT_ID) as Movements
from (
  select distinct EVENT_ID
  from tmp
) as x
go

And then cleanup:

drop table tmp
go
drop function fnConcat
go

This is the result looks like this:

Event ID    Movements
----------- ---------------------------
55001       05 26 28 27 26 27 28 30 40
56215       06 11 22

Upvotes: 1

user330315
user330315

Reputation:

You can do this with a recursive common table expression.

For testing purposes I added a column to the test table that identifies the order of the events in order to be able to identify the first one:

create table movements 
(
  event_id   INTEGER,
  text_fro   VARCHAR(10),
  text_to    VARCHAR(10),
  sort_ord   INTEGER
);

insert into movements (event_id, text_fro, text_to, sort_ord)
values
(55001,null,'05',1),
(55001,'05','10',2),
(55001,'10','15',3),
(55001,'15','20',4),
(55001,'20','30',5),
(56215,'06','11',1),
(56215,'11','22',2)
;

with mvt as (
   select m1.event_id as root_id,
          m1.event_id,
          cast(coalesce(m1.text_fro,'') + ' ' + m1.text_to as varchar(8000)) as path,
          m1.text_fro,
          m1.text_to,
          m1.sort_ord
   from movements m1
   where m1.text_fro is null
   or m1.sort_ord = (select min(sort_ord) from movements m2 where m1.event_id = m2.event_id)

   union all 

   select p.root_id,
          c.event_id,
          p.path + ' ' + c.text_to,
          c.text_fro,
          c.text_to,
          c.sort_ord
   from movements c
     join mvt p on p.text_to = c.text_fro
)
select root_id, max(ltrim(path)) as movements
from mvt
group by root_id;

Here is a SQLFiddle demo

I'm not sure how to deal with cycles in your event loops though.

Upvotes: 1

Related Questions