SQL query to fetch the count for all the possible transitions in a table-CodePudding

Given a set of database records that record the timestamo when an object enters a particular state, I would like to produce a query that shows the count and the list of all the transitions.

I have a table like the following that records the date when an object enters a particular state:

ObjID Timestamp                        State
----- ----------                       -----
A     2022-09-14 09:00:00.000001       1
A     2022-09-14 09:00:00.000002       2
A     2022-09-14 09:00:00.000003       3
A     2022-09-14 09:00:00.000004       4
B     2022-09-14 10:00:00.000001       1
B     2022-09-14 10:00:00.000002       2
C     2022-09-14 11:00:00.000001       1
C     2022-09-14 11:00:00.000002       2

I need to produce a query that returns the count of objects for all the transitions. The result would look like the following:

State                Count
-----                ----
1->2->3->4           1
1->2                 2

As there are two objects for which the state transitions happened from 1 to 2 and one object for which the state transition happen from 1->2->3->4

I have tried below query, but this is will return only transition from 1->2->3->4, but there are rows where the transition can be 1->2->3 or 1->2 or 1->2->3->4->5. So I am not sure how to handle all the permutations in the query.

SELECT COUNT(*)
  from Table T1,
       Table T2,
       Table T3,
       Table T4
 WHERE T1.ObjId = T2.ObjId
   AND T1.ObjId = T3.ObjId
   AND T1.ObjId = T4.ObjId
   AND T1.Timestamp < T2.Timestamp
   AND T2.Timestamp < T3.Timestamp
   AND T3.Timestamp < T4.Timestamp
   AND T1.State = 1
   AND T2.State = 2
   AND T3.State = 3
   AND T4.State = 4

Can anyone please help in this regard.

CodePudding user response：

For DB2, you can use LISTAGG():

See db<>fiddle

WITH cte AS (
SELECT 
    ObjId, 
    LISTAGG(State, '->') WITHIN GROUP (ORDER BY Timestamp) AS State
FROM table
GROUP BY ObjId
)

SELECT
    State,
    COUNT(1) AS Count
FROM cte
GROUP BY State
ORDER BY State DESC