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
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
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.
MAXRECURSION hint, although that requires dynamic SQL if a calculated value is being used (e.g. based on the numbers of rows to be processed). The question then becomes how to abort the query. A CLR function might be useful to assert an error.
05 85 ??null, 06row? Which column will have nulls (if any) and is that how we know the first Movement? Also, will you have data (like before) where a value can occur multiple times for Movement in one row?