Hi,

You can try below query, I have assumed 5 status in this example:

Select
(
B.No,
sum(Status1) as Status1,
sum(Status2) as Status2,
sum(Status3) as Status3,
sum(Status4) as Status4,
sum(Status5) as Status5
from
(
Select
A.No,
Case when Status = 1 Then A.STS_CNT
Else 0 End as Status1,
Case when Status = 2 Then A.STS_CNT
Else 0 End as Status2,
Case when Status = 3 Then A.STS_CNT
Else 0 End as Status3,
Case when Status = 4 Then A.STS_CNT
Else 0 End as Status4,
Case when Status = 5 Then A.STS_CNT
Else 0 End as Status5
from
(
Select No, Status, Count(*) as STS_CNT from table1
group by 1,2
) A
) B
group by 1

Regards,
Balamurugan

I used slightly different data to test - I added an extra Status 3 record in the middle (to valiodate what happens when you have more than two records in a sequence with the same status!).
So my test data was:

111 2009-08-15 2
111 2009-08-16 2
111 2009-08-17 3
111 2009-08-18 3
111 2009-08-19 3
111 2009-08-20 4
111 2009-08-21 2
111 2009-08-22 4
111 2009-08-23 4

The following query gives the answer you need - with or without my extra row!):


Select
"No"
, Sum(Case When PrevStatus = 1 Then 1 Else 0 End) As Status1
, Sum(Case When PrevStatus = 2 Then 1 Else 0 End) As Status2
, Sum(Case When PrevStatus = 3 Then 1 Else 0 End) As Status3
, Sum(Case When PrevStatus = 4 Then 1 Else 0 End) As Status4
, Sum(Case When PrevStatus = 5 Then 1 Else 0 End) As Status5
, Sum(Case When PrevStatus = 6 Then 1 Else 0 End) As Status6
From
(Select "No"
, "Date"
, Status
, Min("Status") Over (Partition By "No"
Order By "Date"
Rows Between 1 Preceding and 1 Preceding) As PrevStatus
From Tbl1
Qualify PrevStatus <> Status
Union
Select
"No", "Date", Status, Status
From Tbl1
Qualify Row_Number()
Over (Partition By "No"
Order By "Date" Desc) = 1) As D1
Group By 1
Order By 1;